How to execute stored procedure from Access using linked tables

Question

I have an Access 2003 database that connects to a SQL Server 2008 box via ODBC. The tables from SQL Server are connected as linked tables in Access. I have a stored procedure on the SQL Server that I am trying to execute via ADO code. The problem I have is that Access cannot seem to find the procedure. What do I have to do within Access to be able to execute this stored procedure? Some facts ...

The stored procedure in question accepts one parameter which is an integer. The stored procedure returns a recordset which I am hoping to use as the datasource for a ListBox.

Here is my ADO code in Access ...

Private Sub LoadUserCaseList(userID As Integer)

  Dim cmd As ADODB.Command

  Set cmd = New ADODB.Command
  cmd.ActiveConnection = CurrentProject.Connection
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "uspGetUserCaseSummaryList"

  Dim par As New ADODB.Parameter
  Set par = cmd.CreateParameter("userID", adInteger)
  cmd.Parameters.Append par
  cmd.Parameters("userID") = userID

  Dim rs As ADODB.Recordset
  Set rs = cmd.Execute()
  lstUserCases.Recordset = rs

End Sub

The error I get is "the microsoft jet database engine cannot find the input table or query "uspGetUserCaseSummaryList".

Answer 1

CurrentProject.Connection is the connection to your Access database. You can verify that by doing this in the Immediate window:

Debug.Print CurrentProject.Connection

You need to create a new ADODB.Connection object with a connection string which points to your SQL Server instance. Have your ADODB.Command object use that connection.

Edit: You can eliminate the ADODB.Command object, and use the Execute method of the connection to return records from your stored procedure. This example uses a stored procedure which expects 3 parameters.

Private Sub GetCenterCodes()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=VM2003\sqlexpress;" _
        & "User ID=foo;Password=bar;Initial Catalog=Inventory"
    cnn.Open
    Set rs = New ADODB.Recordset
    Set rs = cnn.Execute("EXEC uspGetCenterCodes 14, 14, 501")
    Debug.Print rs(0), rs(1), rs(2)
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

You can find a connection string example which matches your needs at ConnectionStrings.com