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