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.c
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;Descript
' 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
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;Descript
' 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
ASKER
Still receiving same error using: ?ParamSPT2("'500'")
what happens if you run "sp_server_info 500" from sql management studio?
ASKER
It completes successfully results: 500 SYS_SPROC_VERSION 9.00.5000:
ASKER
Correction:
It completes successfully from the SQL Management Studio.
It completes successfully from the SQL Management Studio.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ASKER
Quick reply to my initial post and persistent to the end.
Also, is there only 1 parameter for sp_server_info? Including optionals, etc.