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;