How to Simulate Parameters in an SQL Pass-Through Query

Using MS Access, I am trying to pass a parameter to a SQL Server stored procedure by utilizing a pass-through query.  I found information from http://support.microsoft.com/kb/131534

I am receiving a type mismatch error on line" Set MyRS = MyQry.OpenRecordset()
This is what I am entering in the Immediate window: ?ParamSPT2("500")

Can you help me resolve this?
Your help is appreciated.

Here is the code:  

  Function ParamSPT2(MyParam As String)

        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
         ' server.
        MyQry.connect = "ODBC;DSN=EPM_DEV;Description=EPM_Development;UID=abc123;Trusted_Connection=Yes;DATABASE=P Import"
         ' Set the SQL property and concatenate the variables.
         MyQry.SQL = "sp_server_info " & MyParam

         MyQry.ReturnsRecords = True
         Set MyRS = MyQry.OpenRecordset()
         MyRS.MoveFirst

         Debug.Print MyRS!attribute_id, MyRS!attribute_name, MyRS!attribute_value

         MyQry.Close
         MyRS.Close
         MyDb.Close

psueocAsked:
Who is Participating?
 
derekkrommCommented:
Review this KB article and see if it applies to you?

http://support.microsoft.com/kb/181542
0
 
derekkrommCommented:
what if you try ParamSPT2("'500'")?

Also, is there only 1 parameter for sp_server_info? Including optionals, etc.
0
 
psueocAuthor Commented:
Still receiving same error using:  ?ParamSPT2("'500'")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
derekkrommCommented:
what happens if you run "sp_server_info 500" from sql management studio?
0
 
psueocAuthor Commented:
It completes successfully results: 500      SYS_SPROC_VERSION      9.00.5000:
0
 
psueocAuthor Commented:
Correction:
It completes successfully from the SQL Management Studio.
0
 
psueocAuthor Commented:
Awesome problem solved!  http://support.microsoft.com/kb/181542

Thanks for your help.
0
 
psueocAuthor Commented:
Quick reply to my initial post and persistent to the end.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.