Failure to call stored procedure

[JAPA1972]

I have created a stored procedure in SQL server called sp_MyProc which takes no parameters for the sake of simplicity. My Access DB talks to SQL server via an ODBC driver (DamProdSQL) whose parameters are shown below. I have created DamProdSQL with the windows wizard. All my tables in SQL server have been linked to Access via DamProdSQL. One of these tables is ‘DAM’ which I use to retrieve the connection properties. The below code will trigger the ‘ODBC call failed’ error. I am a dbo in SQL server as I am the administrator. What am I missing here? Thanks.


Dim qdef As DAO.QueryDef

Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("DAM").Connect ‘Dam is a table in SQL server which is linked to
‘Access by DamProdSQL
qdef.SQL = "EXEC dbo.sp_MyProc "
qdef.ReturnsRecords = False
qdef.Execute ‘this triggers ‘ODBC call failed’ error


----------This is displayed by the windows wizard when I test the ODBC driver after creating it.

Microsoft ODBC Driver for SQL Server Version 17.05.0001
Data Source Name: DamProdSQL
Data Source Description:
Server: co-nt-dmn6
Use Integrated Security: Yes
Database: FLD
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes


-------------this is displayed by Debug.Print qdef.Connect -------------------------------------

ODBC;DSN=DamProdSQL;UID=MyUserName;Trusted_Connect ion=Yes;APP=Microsoft Office;DATABASE=FLD;

[NoellaG]

Can you link to the SQL tables using the ODBC link? Have you tried calling the SP with a pass-through query?

[ssanfu]

In everything I have read, it says that system stored procedures are named with a "sp_" prefix. When you create a stored procedure, you should use a different name. I use "Usp_" as a prefix.


You didn't say, but did you get "TESTS COMPLETED SUCCESSFULLY!" when you clicked the "Test Data Source" button?

[Minty]

In everything I have read, it says that system stored procedures are named with a "sp_" prefix. When you create a stored procedure, you should use a different name. I use "Usp_" as a prefix.

This is true but they are stored under the sys. schema not dbo.

eg. sys.sp_addrole