create an MS-Access FE with a SQL Server BE without ODBC

  • DaveBriCam

    Hall of Fame

    Points: 3994

    I need to create a set of basic view, insert, update SPs without using ODBC. I have a basic start on an insert SP but need some guidance on same for Viewing existing records... .any ideas/examples?:

    USE [MHF_DB]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    Create Procedure [dbo].[insertPeople]
    (
    @firstName nvarchar(50),
    @lastName nvarchar(50)
    )

    As

    Insert Into peopleInfo
    (
    firstName,
    lastName
    )

    Values
    (
    @firstName,
    @lastName
    )
    GO
  • Matt Miller (4)

    SSC Guru

    Points: 124184

    by without ODBC I am assuming you're meaning no direct linked tables within the Access front-end (you will be using ODBC or OLEDB in the end either way, since they're the communication channel).  That put you in the realm of using Command and recordset objects.  Essentially in the case where you want to SEE data, you're creating the command, then opening a recordset from the command you put together.

    probably best to review this:

    https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/using-the-command-object-access

    As to how to create limit the data - I would design the SP to include the appropriate parameters to filter the query.  you can the populate the parameters on the command object prior to execution/open

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • DaveBriCam

    Hall of Fame

    Points: 3994

    Here's what I'm using in Access to insert a record:

    Private Sub cmdSave_Click()

    Dim strCUser As String
    strCUser = CurrentUserName
    Dim con As adodb.Connection
    Dim cmd As adodb.Command
    Dim rs As adodb.Recordset

    Set con = New adodb.Connection
    Set cmd = New adodb.Command

    con.ConnectionString = CONN_STRING

    con.Open

    cmd.ActiveConnection = con

    cmd.CommandType = adCmdStoredProc

    cmd.CommandText = "insertPeopleContactInfo"

    cmd.Parameters.Append cmd.CreateParameter("@firstName", adVarChar, adParamInput, 40, Me.firstName)
    cmd.Parameters.Append cmd.CreateParameter("@lastName", adVarChar, adParamInput, 40, Me.lastName)

    cmd.Execute

    Call clearParameters(cmd)

    • This reply was modified 4 days ago by  DaveBriCam.
    • This reply was modified 3 days, 23 hours ago by  DaveBriCam.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply