Using SQL Pass-Through Queries

SQL pass-through queries are SQL statements that are sent directly to the database server without interpretation by the Microsoft Jet database engine. SQL pass-through queries provide your application with the ability to use the features of your database server directly. This section describes the common uses of SQL pass-through queries as well as how to design and integrate them into your application.

SQL pass-through query objects are composed of two pieces of information: the SQL statement and the ODBC connection string. The SQL statement is interpreted only by the database server and must use the keywords and syntax of your server’s SQL dialect. Pass-through SQL statements cannot contain Microsoft Jet-specific elements or Visual Basic commands or functions.

See Also For information about operations that cannot be performed by your database server, see “Unsupported Operators and Functions” later in this chapter, or your database server documentation.

You typically use SQL pass-through queries to:

• Perform data definition language (DDL) operations on the server.
  
  
• Take advantage of server-specific features such as stored procedures.
  
  
• Run an SQL query using the database server’s syntax.
  
  
• Run an SQL query that returns multiple result sets.
  
  
• Maintain user accounts or perform other system administration tasks.
  
  
• Run maintenance operations like Microsoft SQL Server’s database consistency checker (DBCC). The DBCC is used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on.
  
  
• Execute multiple INSERT or UPDATE statements in a single batch operation.

When your application executes SQL pass-through queries, Microsoft Jet opens a new connection to the database or uses an existing connection. Then, Microsoft Jet passes the SQL statement directly to the ODBC driver, without interpreting it as a Microsoft Jet SQL query (in other words, the SQL statement can contain commands that are unavailable in Microsoft Jet SQL, provided they are valid in your database server’s SQL language).

The database server interprets the statement and returns the query results (if applicable) and any error or informational messages that arise during query execution. As with other queries, if there are any results to retrieve, they are retrieved on demand as required by the application; this reduces network traffic and ensures that data is only transferred when needed.

Although SQL pass-through queries bypass the Microsoft Jet query processor, they use the Microsoft Jet Recordset processor to create and manage result sets that may be generated by your query. This lets you use foreign-dialect SQL statements to take advantage of your database server’s query processing capabilities while maintaining the power and ease-of-use of the Microsoft Jet Recordset model after the data has been retrieved.

You can create and execute an SQL pass-through query by creating a QueryDef object or by using the Execute method on the Database object.

Note If your SQL pass-through query returns a result set, the Microsoft Jet database engine creates a snapshot-type Recordset object for the results.