I am receiving an error when attempting to run a function that calls a SQL server stored procedure via ms access. It completes successfully when using the sample function which calls sp_server_info. But it displays an error (Run-Time-error 3146 ODBC --call failed) when I try it with a different stored procedure from the same database. So I believe I have eliminated the ODBC connection string as a possible issue. Below is the code with both stored procedures and functions so I can toggle between the sample and production version. I am testing them from the Immediate window as ?ParamSPT2(500) and ?ParamSPT2 respectively.
Your help is appreciated.
Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset
Set MyDb = CurrentDb()
Set MyQry = MyDb.CreateQueryDef("")
' Type a connect string using the appropriate values for your
MyQry.connect = "ODBC;DSN=DEV;Description=EPM_Development;UID=user1;Trusted_Connection=Yes;DATABASE=IBIS Import"
' Set the SQL property and concatenate the variables.
'MyQry.SQL = "sp_server_info " & MyParam
MyQry.SQL = "Get_IBIX_Success_Totals_By_Date"
MyQry.ReturnsRecords = True
Set MyRS = MyQry.OpenRecordset()
Debug.Print MyRS!attribute_id, MyRS!attribute_name, MyRS!attribute_value