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.
Illustration 1: The Tax-Free Pass-Through Arrangement
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.