How to execute stored procedure from Access using linked tables
- by webworm
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".
