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

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4012

    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]



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


    Insert Into peopleInfo

  • Matt Miller (4)

    SSC Guru

    Points: 124203

    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:

    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

    SSCarpal Tunnel

    Points: 4012

    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


    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)


    Call clearParameters(cmd)

    • This reply was modified 4 months, 3 weeks ago by  DaveBriCam.
    • This reply was modified 4 months, 3 weeks 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