With ODBC SQL statements, use pass-through where possible

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 an SQL statement that Microsoft Jet sends directly to the server without stopping to compile the query.

Pass-through queries offer the following advantages over other queries. Some 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 Visual 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 Access Developer's Toolkit.