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

Monday May 3rd 2004 by William Pearson
Share:

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.

For 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 Server 2000.

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 SQL Server

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 database.

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

We 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:

1. Go to the Start button on the PC, and then navigate to the Microsoft Office Access icon, as we did in the first lesson of this series, Create a Calculated Field with the Expression Builder.

2. Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3. Select File -> New from the top menu.

4. Select Blank Database... from the options that are available.

The 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 differ.


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.

5. Type SQL_Server_Front_End into the File Name box of the dialog, after navigating to a place to put the database.

The File New Database dialog appears as depicted in Illustration 3.


Illustration 3: The File New Database Dialog

6. Click the Create button.

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 Window

7. Click the Queries icon in the Objects pane on the left side of the window.

8. Click New atop the Query window that appears, as depicted in Illustration 5.


Illustration 5: Click New ...

The 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.

9. Click-select Design View.

The New Query dialog, with our selection, appears as shown in Illustration 6.


Illustration 6: New Query Dialog - Design View Selected

10.  Click OK.

11.  Click Close to dismiss the empty Show Table dialog box that appears.

Again, 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 design view.

12.  Select Query --> SQL Specific --> Pass-Through on the Query menu, as depicted in Illustration 7.


Illustration 7: Select Query --> SQL Specific --> Pass-Through

The SQL Specific editor appears.

13.  Click the Properties icon atop the view, as partially shown in Illustration 8.


Illustration 8: Click the Properties Icon atop the View (Partial 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 time.

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 environment.

14.  Click Build, the ellipses icon ("...") that appears on the right side of the ODBC Connect Str box on the Query Properties page, as shown in Illustration 9.


Illustration 9: Click the Properties Icon atop the View (Partial View)

The Select 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.

15.  Click the Machine Data Source tab.

16.  Click New.

The Create New Data Source dialog appears.

17.  Select the System Data Source radio button.

The Create New Data Source dialog appears as depicted in Illustration 10.

Click for larger image

Illustration 10: The Create New Data Source Dialog

18.  Click Next.

19.  Scrolling as necessary in the next Create New Data Source dialog that appears ("Select a driver ..."), select the SQL Server driver, as shown in Illustration 11.

Click for larger image

Illustration 11: Select the SQL Server Driver

20.  Click Next.

The third Create New Data Source dialog appears, confirming our selections, as depicted in Illustration 12.


Illustration 12: The Confirmation Dialog

21.  Click Next.

The Create a New Data Source to SQL Server dialog appears.

22.  Type the following in the Name text box of the dialog:

Pubs DB

23.  Type the following in the Description text box of the dialog:

Pubs DB Back End

24.  In the Server selector box, select the server to which you wish to connect (mine is MOTHER in the illustrations).

The Create a New Data Source to SQL Server dialog appears as shown in Illustration 13.


Illustration 13: The Create a New Data Source to SQL Server Dialog

25.  Click Next.

26.  Enter the 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 Setting

27.  Click Next.

28.  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.

29.  Select pubs in the selector box immediately under Change the default database to: , and leave all other settings at default.

The third Create a New Data Source to SQL Server dialog appears as shown in Illustration 15.


Illustration 15: Dialog with Settings

30.  Click Next.

The 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

31.  Click Finish.

The ODBC Microsoft SQL Server Setup dialog appears, confirming our configuration, as depicted in Illustration 17.


Illustration 17: ODBC Microsoft SQL Server Setup Dialog

Let's test the connection at this point to ascertain correct setup.

32.  Click the Test Data Source button on the ODBC Microsoft SQL Server Setup dialog.

The SQL 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

33.  Click OK to close the message box.

34.  Click OK to close the ODBC Microsoft SQL Server Setup dialog that reappears.

We are 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

35.  Ensure that the new data source Pubs DB is selected by clicking / highlighting it.

36.  Click OK.

The Connection String Builder - Save Password dialog appears, as shown in Illustration 20.


Illustration 20: Connection String Builder - Save Password Dialog

Here we can select Yes if we wish to save the logon / password information within the connection string we are building (as appropriate).

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.

Since mine is a development environment, I will save the information, but you may do as you see fit for your own environment.

37.  Answer the dialog by selecting Yes or No, as appropriate to your environment.

We are 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.

Click for larger image

Illustration 21: Query Properties Page - New Connection Information in Place

38.  Close the Query Properties page.

We are 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:

1. Type (or cut and paste) the following query into the SQL Pass-Through Query window:

SELECT CASE
            WHEN ytd_sales IS NULL
                 THEN 'No Data Available'
            WHEN ytd_sales = (SELECT MAX(ytd_sales) FROM titles)
                 THEN 'Top' 
            WHEN ytd_sales = (SELECT MIN(ytd_sales) FROM titles)
                 THEN 'Marginal' 
            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       
FROM titles
ORDER BY ISNULL(ytd_sales, -1) DESC

The SQL Pass-Through Query window appears, with query in place, as shown in Illustration 22.


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.

2. Execute the query by clicking the Run button (see Illustration 23) on the toolbar.


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.

3. Select File --> Save As and name the query ACC012-1, as shown in Illustration 25.


Illustration 25: Saving the Pass-Through Query

4. Click OK.

5. Close the results dataset view.

We see 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

6. Select File --> Exit to leave MS Access as appropriate.

Conclusion ...

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 consumers.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved