ACC: How to Simulate Parameters in an SQL Pass-Through Query

MORE INFORMATION

An SQL pass-through query does not accept dynamic parameters as do other Microsoft Access queries based on attached ODBC tables. This is so because Microsoft Access does not process an SQL pass-through query; it sends the literal commands (written in the syntax required by the SQL server) directly to the server for processing.

To pass parameters to an SQL pass-through query, create a Visual Basic function that builds and runs a QueryDef object using an SQL statement that concatenates the parameter criteria as literal values. Examples 1 and 2 show you how to create the function.

Example 1

This sample function creates a QueryDef object that uses the "sp_addgroup" system procedure from Microsoft SQL Server to add a new group. The new group name is passed to the function and then concatenated into the SQL statement for the QueryDef object. To create this sample function, follow these steps:

1. Create a module and type the following line in the Declarations section:

   Option Explicit

2. Type the following procedure.
   
   Note In the following sample code, you must change UID=<username> and PWD=<strong password> to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database.

         Function ParamSPT (NewGroup As String)
   
            Dim MyDb As Database, MyQ As QueryDef
            Set MyDb = CurrentDB()
   
            ' Create a temporary QueryDef object that is not saved.
            Set MyQ = MyDb.CreateQueryDef("")
   
            ' Type a connect string using the appropriate values for your
            ' server.
            MyQ.connect = "ODBC;DSN=dsn1;UID=<username>;PWD=<strong password>;DATABASE=test"
   
            ' Set ReturnsRecords to false in order to use the Execute method.
            MyQ.returnsrecords = False
   
            ' Set the SQL property and concatenate the variables.
            MyQ.sql = "sp_addgroup" & " " & NewGroup
   
            Debug.Print MyQ.sql
            MyQ.Execute
            MyQ.Close
            MyDb.Close
   
         End Function
   						

3. To run the sample function, type the following line in the Debug window (or the Immediate window in version 2.0), and then press ENTER:

   ? ParamSPT("TESTERS")

   Note that the sample function displays the SQL statement sent to the SQL server for processing and creates a new group called TESTERS.

Example 2

This sample Visual Basic function creates a QueryDef object that uses the "sp_server_info" system procedure from Microsoft SQL Server to display attributes about the server. The attribute's number is passed to the function and then concatenated into the SQL statement for the QueryDef object. To create this sample function, follow these steps:

1. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

2. Type the following procedure.
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         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=user1;UID=user1;PWD=user1;DATABASE=TEST"
   
            ' 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
   
         End Function
   						

3. To run this function, type the following line in the Debug window (or the Immediate window in version 2.0), and then press ENTER:

   ? ParamSPT2("500")

   Note that this function displays the ID, name, and value for the specified attribute number.