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

ID: Q131534

The information in this article applies to:

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates two sample user-defined Visual Basic for Applications functions that you can use to pass parameters to an SQL pass-through query.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q182568
   TITLE     : ACC97: Microsoft Access 97 Sample Queries Available on
               MSL

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

      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=sa;PWD=;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

   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

   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

   (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.

REFERENCES

For more information about the syntax for the SQL property of the SQL pass-through query, see the documentation for your ODBC database server.

For more information about returning values from SQL stored procedures, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q128408
   TITLE     : ACC: How to Return Values from SQL Stored Procedures

Additional query words: spt

Keywords          : kbusage MdlDao 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998