Microsoft KB Archive/210415

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

Article ID: 210415

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210415



Moderate: Requires basic macro, coding, and interoperability skills.

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



SUMMARY
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:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center



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.

 Start Microsoft Access and open any database.  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   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:

Compile error: Argument not optional.

