Pass-Through Queries

Microsoft Jet is designed to work transparently, for the most part, with a wide variety of data sources, whether they’re native Microsoft Jet databases, installable ISAM data sources such as Microsoft FoxPro or Paradox, or ODBC data sources such as SQL Server. Microsoft Jet supports the concept of linked tables — tables that physically reside in an external database, but are treated as though they’re local. When you design a SELECT or UPDATE query, you generally don’t have to worry about whether the tables in the query are native Microsoft Jet tables or are in another data format.

Many data sources have no native SQL interface. Microsoft Jet translates your SQL statement into direct data-manipulation functions that are specific to that data source. Other data sources, such as SQL Server, support the use of SQL by definition, although the dialect of SQL can differ radically from one database management system to another. Even when you create an SQL statement and save it in a QueryDef object, Microsoft Jet transforms the “native” Microsoft Jet SQL statement into a format that you may barely recognize.

Occasionally, you may want to get Microsoft Jet out of the way and directly communicate with your back-end SQL database system. You can use a pass-through query to send any SQL statement to your server. Microsoft Jet does no translation or error checking of any kind: It simply passes the text of the query on to the back-end server for processing.

To create a pass-through query, you create a QueryDef object and set its Connect property to a connection string that contains information about an ODBC data source.

Your SQL statement must conform to the rules of the server you’re using. For example, while Microsoft Jet uses the asterisk (*) character with the Like operator, SQL Server uses the ANSI-standard percent (%) character:

SELECT * 
FROM Products 
WHERE ProductName Like 'M%'

This query acts like a normal SELECT query. It returns records and can be used to create a Recordset object, but it isn’t updatable.

If you save this pass-through query as a permanent QueryDef object, it can even be used to join to other tables or queries. For example, if you save this pass-through query with the name SQLPassThrough, it can be used to build another query, as follows:

SELECT 
	SQLPassThrough.ProductID, 
	SQLPassThrough.ProductName, 
	SQLPassThrough.SupplierID, 
	SQLPassThrough.CategoryID, 
	[Order Details].OrderID
FROM SQLPassThrough, [Order Details];

In this example, the pass-through query SQLPassThrough is executed on the server, which retrieves all records from the Products table whose ProductName field begins with an “M.” The result of the SQLPassThrough query is joined with the Order Details table, and the final result is created transparently.

Anything that the back-end server can interpret can be successfully used in a pass-through query. This includes DDL statements that create and delete tables, or DML DELETE and INSERT INTO statements that don’t return records but instead act directly on the back-end server database.

The following code creates a QueryDef object for a pass-through query and sets the QueryDef object’s Connect property. In this example, strDbPath is the path to the database, strQueryName is the name for the new query, strSQL is the SQL statement that defines the QueryDef object, and strConnect is the connection string information used to connect to the ODBC data source:

Dim dbs As Database, qdf As QueryDef
    
Set dbs = OpenDatabase(strDbPath)
' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef(strQueryName, strSQL)
' Setting Connect property indicates that query
' is a pass-through query.
qdf.Connect = strConnect

An SQL pass-through query may return records to the client computer, or it may perform an operation on the server and not return any records to the client. For example, a SELECT query returns records, while an action query performs an operation on the server and does not return any records. The ReturnsRecords property indicates whether the query is expected to return records. By default Microsoft Jet assumes that a pass-through query will return records, and the ReturnsRecords property is set to True. If a pass-through query will not return records but simply performs an operation on the server, you must set the ReturnsRecords property to False. If you try to run an action query whose ReturnsRecords property is set to True, an error occurs.

The contents of a pass-through query don’t have to be an SQL statement. You can execute stored procedures. If a stored procedure returns records, the pass-through query that executes it can be used to create a Recordset object. For example, if this query were saved as a pass-through QueryDef object called SQLPassThroughStoredProc, it could be used in another query:

SELECT SQLPassThroughStoredProc.*
FROM SQLPassThroughStoredProc
WHERE SQLPassThroughStoredProc.Status = 'sleeping'; 

You can supply parameters to a stored procedure by concatenating the value of the parameter with the call to the stored procedure. The following example creates a simple SQL Server stored procedure with an input parameter and a return value. It then runs the procedure and retrieves the return value. In this example, strSQL is the remote query definition, cnn is a variable that represents an ODBCDirect Connection object, qdf is the QueryDef object, and var is a variant that contains the value of the parameter for the QueryDef object:

' Create stored procedure on the server.
strSQL = "CREATE PROCEDURE UpdateEmps (@invar int) AS RETURN @invar;"
cnn.Execute strSQL

' Create QueryDef object to run stored procedure.
Set qdf = cnn.CreateQueryDef("qry", "{ ? = call UpdateEmps(?) }")

' Handle parameters.
qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1) = 10
qdf.Execute

' Get return value.
var = qdf.Parameters(0).Value

See Also For more information about ODBC, see Chapter 12, “ODBC Desktop Database Drivers.”