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

Article translations Article translations
Article ID: 232493 - View products that this article applies to.
This article was previously published under Q232493
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SUMMARY

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.

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center

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 because Microsoft Access does not process an SQL pass-through query; it sends the literal commands (written in the syntax required by 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.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

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 or paste the following procedure:
    Function ParamSPT (NewGroup As String)
    
       Dim MyDb As DAO.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=DSNName;UID=UserName;PWD=Password;DATABASE=DatabaseName"
    
       ' 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 Immediate window, and then press ENTER:
    ? ParamSPT("TESTERS")
    Note that the sample function displays the SQL statement sent to the SQL Server computer 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 SQL Server to query the spt_server_info system table 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 or paste the following procedure.NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character.
    Function ParamSPT2(MyParam As String)
    
       Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
       Set MyDb = CurrentDB()
       Set MyQry = MyDb.CreateQueryDef("")
    
       ' Type a connect string using the appropriate values for your
       ' server.
       MyQry.connect = "ODBC;DSN=DSNName;UID=UserName;PWD=Password;DATABASE=DatabaseName"
    
       ' 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 Immediate window, and then press ENTER:
    ? ParamSPT2("2")
    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 pass-through queries, click Microsoft Access Help on the Help menu, type send commands to an sql database using a pass-through query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 232493 - Last Review: January 26, 2005 - Revision: 2.1
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo KB232493

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com