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

Since mine is a development environment, I will save the information, but you may do as you see fit for your own 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.

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:

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.

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.

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.

We thus see the results of processing a SQL Server - specific query against a SQL Server database using a Pass-Through query.

We see the new Pass-Through query displayed, alongside the characteristic icon, as shown in Illustration 26.

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