Call stored procedure in SSMS from Access 2016 doesn't work.

Dec 15, 2015
Reaction score

When I execute the code below then I get an error.
Function Get_Recordset_Customer(CustomerID)
Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim paramx As ADODB.Parameter

Set cn = CurrentProject.AccessConnection
Set paramx = cmd.CreateParameter("@CustomerID", adInteger, adParamInput)
paramxValue = CustomerID
cmd.Parameters.Append paramx

With cmd
.ActiveConnection = cn
.CommandText = "sp_Customer"
.CommandType = adCmdStoredProc
Set Get_Recordset_Customer = .Execute
End With
End Function

The stored procedure "sp_Customer" is present in SQL Server Management Studio.

The message i get is :
"The Microsoft Access database engine cannot find the input table or query 'sp_Customer'. Make sure it exists and that its name is spelled correctly".

The initial database in which this code was developed was an Access-adp database and everything worked perfectly there.

I can access my tables in SSMS via Access. This is no problem.
This instruction is wrong : Set Get_Recordset_Customer = .Execute , because this lines works in adp but not in accdb.
That's why I created a query in my Access Database with the same name as the Stored Procedure in SSMS and then my code works. So no connection is made with SSMS and I can't find why. I have already tried many scenarios but none of them have succeeded.
My code is a call to the Stored Procedure in SSMS with a parameter and places it (always 1 record) in the record set of a subForm in Access :
Set Me! FrmAnalyse_Analyse_List.Form.Recordset = Get_Recordset_Customer(Me! CustomerID)
What am I doing wrong.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question