Using Pass-Through Queries

One of the major enhancements to Microsoft Jet 2.0 was the ability to create pass-through queries. With an ordinary query, Microsoft Jet compiles and runs the query, combining the power of the server with the capabilities of the Jet database engine. With a pass-through query, you enter a SQL statement that Microsoft Jet sends directly to the server without stopping to compile the query.

Pass-through queries offer some advantages over other queries. A few of the more important ones are:

Pass-through queries can use server-specific functionality — such as stored procedures and server-based intrinsic functions — that have no Microsoft Basic equivalent.
Pass-through queries can log informational messages returned by the server, such as warnings and statistics.
Update, delete, and append pass-through action queries are faster than action queries based on attached remote tables, especially when many records are affected. However, a pass-through action query cannot partially succeed as an ordinary action query can. (In addition, you are limited to action queries that your server can run; if an action query uses multiple tables, you may have no choice but to use an ordinary query.)

Full details on pass-through queries can be found by searching Microsoft Access Help for SQL: pass-through queries. See also the Developing Client/Server Applications chapter of Building Application in the Microsoft Access Developer's Toolkit.

Creating a Pass-through Query

The DAO code following creates a simple pass-through query calling Microsoft SQL Server™ SP_WHO stored procedure:

Dim MyWorkspace As WorkSpace, MyDB As Database, Myquery As QueryDef, MySet As Recordset
Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)
Set Myquery = MyDB.CreateQueryDef("")
Dim SourceConnectStr As String
SourceConnectStr = "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=nwind"
Myquery.Connect = SourceConnectStr
Myquery.ReturnsRecords = True
Myquery.SQL = "sp_who"

 'Now create the snapshot and print out the returned data
Set MySet = MyQuery.OpenRecordset()
Dim I As Integer, J As Integer
Debug.Print
' Enumerate fields.
Debug.Print "Fields: Name, Type, Value"
For I = 0 To MySet.Fields.Count - 1
    Debug.Print "  "; MySet.Fields(I).Name
    Debug.Print ", "; MySet.Fields(I).Type
    Debug.Print ", "; MySet.Fields(I).Value
Next I

Returning Multiple Result Sets

Some ODBC servers such as SQL Server have the ability to return more than one set of results by running an arbitrary SQL statement. Although the DAO model is not designed to return more than one Recordset, it is still possible to retrieve the extra data returned.

The typical scenario where you will get more than one result set is if you execute a stored procedure using a SQL pass-through query. For example, you could run the following code that would try and return multiple result sets using the SQL Server Pubs database:

Dim MyDB As Database, Myquery As QueryDef, MySet As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Myquery = MyDB.CreateQueryDef("")
Myquery.Connect = "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=pubs"
Myquery.ReturnsRecords = True
' normally you would invoke a stored procedure below
' e.g. Myquery.SQL = "mystoredproc"
Myquery.SQL = "select * from Authors; select * from titles;"
Set MySet = Myquery.OpenRecordset()

This code will only return a snapshot type recordset with the set of records in the Authors table. If you wish to see the records in the second set of records, the Titles records, then you need to do the following:

Make a pass-through query that runs the stored procedure or executes a multiple result returning set of SQL statements (as in the above example).
Run a Select Into (aka maketable) query that uses the pass-through query as its input

Building on the above example by creating a saved query instead of a temporary query, we get:

Dim MyDB As Database, Myquery As QueryDef, MySet As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Myquery = MyDB.CreateQueryDef("GetAuthorsTitles")
Myquery.Connect = "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=pubs"
Myquery.ReturnsRecords = True
Myquery.SQL = "select * from Authors; select * from titles;"

MyDB.Execute "SELECT * INTO Results FROM GetAuthorsTitles"

Set MySet = MyDB.OpenRecordset("Results")
...
Set MySet = MyDB.OpenRecordset("Results1")
...

The tables in the local database are regular tables that support all the normal table operations. Normally you would use these tables to browse the data returned and then delete them in preparation for the next time you run the Select Into query. Note that you only need to create the "GetAuthorsTitles" query once. Similarly you may choose to save the Select Into query rather than having it embedded in code. Also, you must use "Select *" to multiple tables returned – you cannot select specific fields (even if they occur in all the result sets).

Returning messages

Some stored procedures return messages. For example, on SQL Server, you can embed print statements in stored procedures that return arbitrary strings. To capture these messages using DAO, you can create queries that when executed will store the messages in a table.

The name of the messages table where the returned messages are stored is username - nn, where username is the login name of the user running the pass-through query, and nn is a number that increases in increments of 1, starting at 00. For example, if user JoanW sets the LogMessages property to Yes and receives messages from a SQL database, the messages table will be named JoanW - 00. If JoanW receives messages in another Microsoft Access session (and the first table hasn't been deleted), a new table named JoanW - 01 is created.

The following code shows an example of a stored procedure returning a message into the default login table of Admin - 00:

Dim MyDB As Database, Myquery As QueryDef, MySet As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Myquery = MyDB.CreateQueryDef("Test")
Myquery.Connect = "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=pubs"
Myquery.ReturnsRecords = False
Myquery.SQL = "print ""hi there"""

' Create user property logmessages to log messages
Dim MyProperty As Property
Set MyProperty = Myquery.CreateProperty("logmessages", db_boolean, False)
Myquery.Properties.Append MyProperty
Myquery.Properties("logmessages") = True

Myquery.Execute

Set MySet = MyDB.OpenRecordset("admin - 00")
...

In the table "Admin - 00" will be the single record

01000 - 0 - [Microsoft][ODBC SQL Server Driver][SQL Server] hi there.

This format contains the ODBC error code (or in this case informational code) the driver used and finally the actual message generated by the SQL Server Print command.