Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted May 3, 2004

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

By William Pearson

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:

            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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM