How to execute stored procedure from Access using linked tables

Posted by webworm on Stack Overflow See other posts from Stack Overflow or by webworm
Published on 2010-06-15T21:54:04Z Indexed on 2010/06/15 22:02 UTC
Read the original article Hit count: 240

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".

© Stack Overflow or respective owner

Related posts about ms-access

Related posts about stored-procedures