MS Access for the Business Environment: Extend Access with Pass-Through Queries - Page 2

Using Pass-Through Queries in MS Access

Pass-Through queries are coded in the dialect of the back end database. Because the syntax is specifically intelligible to the targeted database server, there is no need for the query to be translated by the MS Access Jet engine. The absence of a need to be translated is the chief advantage of using a Pass-Through query, as we shall see.

When we go the route of simply linking tables between MS Access and the back end database server, we obtain an easy process of setting up communication between the two. But, by its nature, a link generates what I like to refer to as a costly transactional tax; I like this term because I find that describing overhead this way tends to make people consider its perniciously recurring nature. The Jet engine translates an MS Access SQL expression to an ODBC SQL expression. The ODBC driver in turn translates the ODBC SQL to a generic SQL expression that is suitable for the back-end server. The translation process adds considerable overhead to every "transaction" that must undertake it. What's more, the transactions' location of processing is enforced at the client level, when it might been far more desirable that it transpire on the backend server, where we tend to want to direct as much processing as possible.

Combining the obvious impedance that the translation process adds to the likelihood that what is optimized from the front end perspective is not optimized for the back end database server might be enough to discourage the use of linking as basis for a client / server relationship. But if these disadvantages are not sufficient motivation to go the Pass-Through query route instead, it might be wise to add another consideration to the mix: other differences between MS Access SQL and the SQL of the back end might mean dissimilarities in the support the two options offer for activities that we are attempting to accomplish.

Pass-Through queries offer advantages in the context of the performance hit that linking is almost certain to bring. Let's take a look at some of the "tax relief" we might associate with using Pass-Through queries to support our front-end-to-back-end communications, as well as some of the less-than-optimal attributes that might also need to be factored into our design efforts.

Overview of Pass-Through Queries in MS Access

With the foregoing considerations exposed, one can see that linking is not, in most cases, an optimal arrangement for client / server communication. The Pass-Through query, in most cases, can outperform the linking arrangement on the merit of one consideration alone: it is coded in the syntax of the server, and thus avoids the "translation tax" altogether. After simply using the Jet engine as a conduit to reach ODBC, it also transits ODBC without any translation. Illustration 1 shows the path that the query takes, unmassaged, to the final back-end RDBMS destination.

No translation is required, because no change need be made in the syntax to make it intelligible to the back end server. We still have to transit the Jet and ODBC components, but, again, they do little other than "pass through" the query from the client. One obvious consequence of this is the fact that the query must be syntactically correct from the perspective of the server, and therefore will not usually be "homegrown" MS Access SQL. While this is a relief to those of us that live in an enterprise RDBMS world, and work with SQL there routinely, it might present a challenge to those whose background is in MS Access' SQL dialect. However, in most cases, whether a developer is creating a new client / server application, upgrading or upsizing (to MSSQL Server 2000) an existing MS Access solution, or otherwise writing queries to go against a larger back-end server (for that matter, even writing reports, etc., against an enterprise RDBMS), knowledge of SQL suddenly attains an aura of power anyway. If you are working with the likes of MSSQL Server or its ilk as a back end in the near future, you can only benefit by understanding how to speak its language.

Now let's look at some of the sobering considerations of taking this approach. Pass-Through query will still be the clear leader among the options, but I would be remiss to pave the road entirely with gold (we know where golden roads wind up, don't we?). The first consideration actually lies in the strength of the Pass-Through Query: the fact that it is "passed through" SQL that needs to be completely correct in the dialect of the targeted back-end environment. This leaves no room for error, and reliance upon the same translation process that we described as "tax heavy" above must be abandoned. Thus, the "training wheels" must be discarded, and the coding has to be precise for the back-end RDBMS.

Back-end-specific SQL also means that, should the back-end environment change in any way, coding will have to change as well - the front end obviously cannot dynamically flex. But if change is expected to be minimal, or to afford us the advance notice we need to be able to prepare a relatively small component of the whole picture, Pass-Through queries are still an appropriate choice, even from this perspective.

Other considerations are weightier to some, but still not usually overly pessimistic - certainly not ponderous enough to overtake the savings that we accrue by avoiding the dual taxation rendered unto the Jet / ODBC partnership under a linking scenario. But let us keep in mind the nature of a Pass-Through query, and not overlook the fact that it returns a data snapshot. "Snapshot" is a great description of the returned dataset, as it is fixed, and cannot be updated. If this, coupled with the fact that the Pass-Through query cannot be parameterized, are not instant put-offs, then the Pass-Through query might be a comfortable solution for enabling the client and server to talk with each other.