Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Pass-Through Queries

Pass-Through queries enable you to send uninterpreted SQL statements to your back-end database when you're using something other than the Jet Engine. These uninterpreted statements are in the SQL that's specific to your particular back end. Although the Jet Engine sees these SQL statements, it makes no attempt to parse or modify them. Pass-Through queries are used in several situations:

• The action you want to take is supported by your back-end database server, but not by Access SQL or ODBC SQL.

• Access or the ODBC driver is doing a poor job parsing the SQL statement and sending it in an optimized form to the back-end database.

• You want to execute a stored procedure on the back-end database server.

• You want to make sure the SQL statement is executed on the server.

• You want to join data from more than one table residing on the database server. If you execute the join without a Pass-Through query, the join is done in the memory of the user's PC after all the required data has been sent over the network.

Although Pass-Through queries offer many advantages, they aren't a panacea. They do have a few disadvantages:

• Because you're sending SQL statements specific to your particular database server, you must write the statement in the "dialect" of SQL used by the database server. For example, in writing a Pass-Through query to access SQL Server data, you must write the SQL statement in T-SQL. When writing a Pass-Through query to access Oracle data, you must write the SQL statement in PL-SQL. This means that you'll need to rewrite all the SQL statements if you switch to another back end.

• The results returned from a Pass-Through query can't be updated.

• The Jet Engine does no syntax checking of the query before passing it on to the back end.

Now that you know all the advantages and disadvantages of Pass-Through queries, you can learn how to build one: