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

MORE INFORMATION

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.

? CreateSPT("Test", "Select * from authors",_ 
 "ODBC;DSN=Red;Database=Pubs;USID=JOE;PWD=JOE")
				

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: