Go directly to the back-end database to optimize query processing.
Bill Pearson leads a hands-on introduction to implementing Pass-Through queries
to MSSQL Server 2000 from an MS Access client.
About the Series ...
This article continues the series, MS Access for the
Business Environment. The primary focus of this series is an
examination of business uses for the MS Access relational database management
system. The series is designed to provide guidance in the practical application
of data and database concepts to meet specific needs in the business world. The
majority of the procedures I demonstrate in this article and going forward will
be undertaken within MS Access 2003, although most of the concepts that
we explore in the series will apply to earlier versions of MS Access, as well.
more information on the series, as well as the hardware / software requirements
to prepare for the tutorials we will undertake, please see Tutorial
1: Create a Calculated Field with the Expression Builder. Along with MS Access, of which we have made
repeated use in the previous articles of the series, additional application considerations
apply for this tutorial, because it introduces another Microsoft RDBMS, MSSQL
For those joining the
series at this point because of a desire to work with MSSQL Server 2000 and
its components from an MSSQL Server perspective, it is assumed that MSSQL
Server 2000 is accessible to / installed on your PC, with the appropriate
access rights to the MSSQL Server 2000 environment to parallel the steps of the
article. If this is the first time MSSQL Server 2000 is being accessed on your
machine, you may need to consult the MSSQL Server 2000 online documentation for
installation and configuration instructions.
Introduction to this Tutorial
This article focuses on a topic
that is related to an earlier group of articles we have published, all of which
dealt with the use of "SQL specific" or "direct SQL"
within MS Access. As many of us are aware, MS Access can play many varied
roles in client / server architecture, and it is quite common to find it acting
as a front end to various enterprise-level, back-end databases. MSSQL
Server is a natural for the back-end partner in such arrangements, and we
can manage communication between MS Access and MSSQL Server in multiple ways.
This flexibility includes using links, ActiveX data objects, or SQL
Pass-Through queries to allow communication between MS Access and
In this article, we will devote
our efforts to the latter of the three options, and concentrate on the use of Pass-Through
queries as the medium of communication. Our examination of Pass-Through
queries will include the following:
A discussion of the nature of Pass-Through
queries, and instances in which their use is warranted;
A discussion of the advantages and
disadvantages incumbent within the choice to use Pass-Through queries;
A hands-on practice exercise that
includes the creation and operation of a Pass-Through query to a MSSQL Server 2000
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.
Practice: Establish MS Access as a Front End to MSSQL Server 2000
will be establishing the Pubs sample database, which is installed with
MSSQL Server 2000 as a part of a typical installation, as the back end for the
present exercise. To do so, we need to specify the target database particulars
and create a connection between MS Access and SQL Server.
Let's start MS Access
and proceed with the preparation for building a Pass-Through query to MSSQL
Server 2000, specifically with the establishment of a data connection to the
targeted Pubs database, taking the following steps:
Access opens, and may display the initial dialog. If so, close it.
-> New from the top menu.
Database... from the options that are available.
selection can be made from the Task Pane in MS Access 2003, as shown in Illustration
2, among other ways. Depending upon your version of Access, this may
Illustration 2: Select Blank Database ... (Compact View)
The File New Database dialog appears. Here we give
the new database a name and designate where we wish to place it.
Type SQL_Server_Front_End into the File Name box
of the dialog, after navigating to a place to put the database.
New Database dialog appears as depicted in Illustration 3.
Illustration 3: The File New Database Dialog
Click the Create
The new database is created, and we arrive at the Database
window, as shown in Illustration 4.
Illustration 4: The New Database Opens in the Database
Click the Queries
icon in the Objects pane on the left side of the window.
Click New atop
the Query window that appears, as depicted in Illustration 5.
Illustration 5: Click New ...
Query dialog appears, from which we can select the means by which we create
our query. Because we must use syntax specific to MSSQL Server 2000, as we
discussed earlier, we will not be able to use wizardry at this juncture.
Query dialog, with our selection, appears as shown in Illustration 6.
Illustration 6: New Query Dialog - Design View Selected
to dismiss the empty Show Table dialog box that appears.
we cannot rely upon the graphical query design tools, as we must concoct a
query using SQL that meets the dialectal requirements of the back-end RDBMS.
We will need to leave the graphic query builder and get to the SQL Specific
--> SQL Specific --> Pass-Through on the Query menu, as
depicted in Illustration 7.
Illustration 7: Select Query --> SQL Specific -->
The SQL Specific editor appears.
Click the Properties
icon atop the view, as partially shown in Illustration 8.
Illustration 8: Click the Properties Icon atop the View
The Query Properties page opens. Here we will assign
connection information for the back-end server. We might have created ODBC
connections in advance, but we will walk through the complete process here, as
if we were doing it "on the fly," so as to cover all the bases. For
more information on establishing an ODBC connection, see the MSSQL
Server documentation, MS Access documentation, or other relevant sources.
Note that if we do not specify a connection string here, MS
Access will use the default that initially appears, "ODBC." If we
take that route, we will be prompted for connection information at query run
NOTE: Some of the connection information that
I depict in the illustrations will obviously need to be supplanted with
settings that are contextually correct from the perspective of your own
the ellipses icon ("...") that appears on the right side of the ODBC
Connect Str box on the Query Properties page, as shown in Illustration
Illustration 9: Click the Properties Icon atop the View
Data Source dialog appears, defaulted to the File Data Source tab.
While there are various options for the types of source we can establish, we
will create a Machine Data Source at this point.
Click the Machine
Data Source tab.
New Data Source dialog appears.
Select the System
Data Source radio button.
New Data Source dialog appears as depicted in Illustration 10.
necessary in the next Create New Data Source dialog that appears ("Select
a driver ..."), select the SQL Server driver, as shown in Illustration
third Create New Data Source dialog appears, confirming our selections, as
depicted in Illustration 12.
Illustration 12: The Confirmation Dialog
a New Data Source to SQL Server dialog appears.
following in the Name text box of the dialog:
following in the Description text box of the dialog:
Pubs DB Back End
In the Server
selector box, select the server to which you wish to connect (mine is MOTHER
in the illustrations).
a New Data Source to SQL Server dialog appears as shown in Illustration
Illustration 13: The Create a New Data Source to SQL
appropriate authentication setting for security by clicking the respective
radio button in the next dialog. (Mine is Windows NT authentication, as
depicted in Illustration 14.) Leave the other settings at default.
Illustration 14: Select the Appropriate Authentication
On the next
dialog to appear, click the radio button, named 'Change the default database
to:' to the left of the top entry, to activate it.
in the selector box immediately under Change the default database to: ,
and leave all other settings at default.
third Create a New Data Source to SQL Server dialog appears as shown in Illustration
Illustration 15: Dialog with Settings
next dialog of the series appears. We will leave all setpoints therein at
default, as shown in Illustration 16.
Illustration 16: Fourth Dialog, Create a New Data Source to
SQL Server Series
Microsoft SQL Server Setup dialog appears, confirming our configuration, as
depicted in Illustration 17.
Illustration 17: ODBC Microsoft SQL Server Setup Dialog
test the connection at this point to ascertain correct setup.
Click the Test
Data Source button on the ODBC Microsoft SQL Server Setup dialog.
Server ODBC Data Source Test message box appears momentarily, indicating we
have tested positive for connectivity, as shown in Illustration 18.
Illustration 18: SQL Server ODBC Data Source Test Message
Box - Connectivity Confirmed
to close the message box.
to close the ODBC Microsoft SQL Server Setup dialog that reappears.
returned to the Select Data Source dialog, Machine Data Source tab,
where we can see our new data source, Pubs DB, appear among the
selections, as shown in Illustration 19.
Illustration 19: Select Data Source Dialog - Machine Data
Source Tab, with Our New Source
the new data source Pubs DB is selected by clicking / highlighting it.
String Builder - Save Password dialog appears, as shown in Illustration
Illustration 20: Connection String Builder - Save Password
Here we can select Yes if we wish to save the logon /
password information within the connection string we are building (as
NOTE: Saving logon information in connection
strings carries inherent risk. Security of the data source may be compromised
by doing so. For more information, consult the MS Access documentation, MSSQL
Server 2000 Books Online, or other resources. Not saving the information
will result in our being asked for logon / password information each time we
attempt to access the back end via MS Access.
mine is a development environment, I will save the information, but you may do
as you see fit for your own environment.
dialog by selecting Yes or No, as appropriate to your
returned to the Query Properties page, where we see the new string specification
appear in the ODBC Connect Str box, as shown in Illustration 21.
Close the Query
now ready to plug in the Pass-Through query itself.
Practice: Build and Execute a Pass-Through Query in MS Access
Our next steps will surround the creation of a SQL Server
2000-specific query, and its placement in the SQL Pass-Through Query
window. To begin, let's assume that we have been contacted by a group of
information consumers in the accounting department of our organization (a book
publishing concern), with a relatively simple business need: The group needs
to produce a report that presents the year-to-date sales volume information for
the titles under their responsibility, compared to the general population of
titles stocked. The wish to state the comparison results in terms of top,
marginal and average sales volumes.
We will meet the needs of the information consumers by
taking the following steps:
Type (or cut
and paste) the following query into the SQL Pass-Through Query
WHEN ytd_sales IS NULL
THEN 'No Data Available'
WHEN ytd_sales = (SELECT MAX(ytd_sales) FROM titles)
WHEN ytd_sales = (SELECT MIN(ytd_sales) FROM titles)
WHEN ytd_sales < (SELECT AVG(ytd_sales) FROM titles)
THEN 'Below Average'
ELSE 'Average Plus'
END 'YTD Volume',
Code = title_id,
Title = SUBSTRING(title, 1, 40),
Price = price
ORDER BY ISNULL(ytd_sales, -1) DESC
Pass-Through Query window appears, with query in place, as shown in Illustration
Illustration 22: SQL Pass-Through Query Window, with our
Query in Place
The query with which we seek to interact directly with the
server-based Pubs database is now in place. Our query will avoid the "taxes"
we discussed above by bypassing processing within the Jet engine and ODBC,
merely flowing through them to the back end RDBMS instead.
For more information on the structure of the SQL query, see
the MSSQL Server Books Online or other MSSQL Server SQL references. Our
purpose here is to investigate the pass-through nature of the query, rather
than to review basic SQL syntax.
query by clicking the Run button (see Illustration 23) on the
Illustration 23: Execute the Query using the Run Button
Had we not saved the connection information, or had we
forgone the insertion of the connection string in the ODBC Connect Str
box of the Query Properties page, we would be prompted for
connection / logon information at this point.
The query runs and the results dataset is returned, as shown
in Illustration 24.
Illustration 24: The Results Dataset Appears
We thus see the results of processing a SQL Server -
specific query against a SQL Server database using a Pass-Through query.
--> Save As and name the query ACC012-1,
as shown in Illustration 25.
Illustration 25: Saving the Pass-Through Query
results dataset view.
the new Pass-Through query displayed, alongside the characteristic icon, as
shown in Illustration 26.
Illustration 26: The New Query Appears in the Query View
--> Exit to leave MS Access as
In this article, we returned to
an earlier subject in some respects, the use of an "SQL specific" or "direct
SQL" query within MS Access. Focusing on the roles that MS Access can
play in client / server architecture, particularly as a front end to an
enterprise-level, back-end database (our present choice being MSSQL Server
2000), we discussed options with regard to managing communication between MS
Access and MSSQL Server. Of these options, we narrowed our attention to SQL
Pass-Through queries as the medium of communication.
We discussed the nature of
Pass-Through queries, and instances in which their use is warranted. Next, we
exposed advantages and disadvantages inherent to the use of Pass-Through
queries. We then activated the concepts we had introduced with an illustrative
practice exercise, based upon a hypothetical business need. We established a
data source connection to an MSSQL Server 2000 database in preparation.
Finally, we created, and successfully executed, a Pass-Through query to the
designated back-end database to meet the illustrative requirement of the information
See All Articles by Columnist William E. Pearson, III