ACC2000: How to Create an SQL Pass-Through Query Using Data Access Objects

This article demonstrates how to create an SQL pass-through (SPT) query in Microsoft Visual Basic for Applications with Data Access Objects (DAOs). You can use SPT queries to pass SQL statements directly to an ODBC data source, avoiding the need to link tables.

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:

You can use SPT queries to send commands directly to an ODBC database server (such as Microsoft SQL Server). Using Visual Basic, you can write a function that creates an SPT query. When you run the query, it sends commands directly to the ODBC database server for processing.

To create an SPT query in code, follow these steps:

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.

1. Start Microsoft Access and open any database.
2. Create a new module and paste or type the following code:

   Function CreateSPT (SPTQueryName As String, SQLString As String, _
               ConnectString As String)
   '-----------------------------------------------
   ' FUNCTION: CreateSPT()
   ' PURPOSE:
   '   Creates an SQL pass-through query using the supplied arguments:
   '      SPTQueryName: the name of the query to create
   '      SQLString: the query's SQL string
   '      ConnectString: the ODBC connect string, this must be at
   '         least "ODBC;"
   '-----------------------------------------------
      Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
   
      Set mydatabase = DBENGINE.Workspaces(0).Databases(0)
      Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
   
      myquerydef.connect = ConnectString
      myquerydef.sql = SQLString
      myquerydef.Close
   End Function
   					

3. To test this function, type the following line in the Immediate window, and then press ENTER:

   ? CreateSPT("MySptQuery", "sp_help", "ODBC;")
   					

This creates the query MySptQuery and adds it to the list of queries in the Database window. When you run MySptQuery, it prompts you for the ODBC connect string and runs the stored procedure sp_help, which returns a list of tables from SQL Server.

To create an SPT query called Test that returns a list of all the records from the Authors table (located on the Red server in the Pubs database), type the following code in the Immediate window:

NOTE: In the following example, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the example. This example also includes the UserId and Password arguments (both "Joe") in the ODBC connect string, because the DSN configuration in the example requires SQL Server authentication rather than Windows NT authentication.

Note that if you do not supply at least "ODBC;" as the connect string, you receive the following error message:

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 topics returned.

For additional information about using pass-through queries, click the article number below to view the article in the Microsoft Knowledge Base: