Trouble executing Stored Procedure

[Bill Sturdevant]

I have a stored procedure in SQL Server that looks for a bunch of parameters.
It inserts a record into a table, but in doing so, it does lookups in
secondary tables to translate the input parameters into IDs from the
secondary tables.

It works correctly when I execute it in SQL Server.

I assemble the SQL of an Access SQL Pass-thru query as follows (abbreviated):
"EXEC MySQLServerStoredProc @Param1='401', @Param2='XXXXX', etc. etc." in a
variable called strSQL.

Then I run the following code.

Dim dbsODBC As Database, strConnect As String
strConnect = "ODBC;Driver={SQL server};SERVER={" & strServerName &
"};DATABASE={" & strOrganizationShortName & "};Trusted_Connection=Yes;"
Dim qdf As QueryDef
Set qdf = CodeDb.QueryDefs("Access Pass-thru Query Name")
qdf.SQL = strSQL
qdf.Connect = strConnect

When I stop my code here at a breakpoint and open the Access Pass-thru Query
and Run it using the Run button, it executes properly.

But, I am having trouble coming up with the correct code to run it from VBA...

I cannot figure out what command goes next!! Can anyone help?

Here is what I have tried so far:
1. qdf.Execute "Access Pass-thru Query Name" gives me a Data Type Conversion
error.
2. CodeDb.Execute "Access Pass-thru Query Name" gives me an error 3065:
Cannot execute a select query.
3. CodeDB.Execute strSQL gives me an error 3129: Invalid SQL statement;
Expected 'DELETE', 'INSERT', 'PROCEDURE', "SELECT' or 'UPDATE'

 

[Ofer]

To run the query, you can use
Docmd.openQuery "Access Pass-thru Query Name"

Can you post the sql you have in the query, that run fine

 

[Ron Weiner]

How about loosing the PassThru query and executing the Sproc via an ADO
connection.

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strsql As String, strCon As String

strSql = EXEC MySQLServerStoredProc @Param1='401', @Param2='XXXXX',
etc. etc."
strCon = "Whatever works as the connection string for your situation"
Set cn = New ADODB.Connection
cn.ConnectionString = strCon
cn.Open
Set rs = cn.Execute(strSql)
' do whatever you do with the records returned
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing