Building a Pass-Through Query in Access 97
This is a short, step-by-step description of creating a Pass-Through Query in Access 97. A Pass-Through Query is an Access 97 stored object that is designed with the SQL of the Database Management System that Access links to via the File > Get External Tables > Link Tables… command. This SQL “passes through” the Access platform and communicates directly with the DBMS. For example, if you are linking to Oracle you can use PL/SQL in Access to execute one and only one SELECT statement—the same statement used in a product like SQL*Plus.
For detailed information about Pass-Through queries please choose Help > Contents and Index > Index and enter “pass-through” in box 1. This is what your dialog may look like:
Click on the Display button to read the help file(s).
Getting Ready to Design the Pass-Through Query
In the Database Window, click on the Queries tab. Your window may look like this:
Click on the New button or type Alt-N.
When the New Query dialog appears, select Design View and click OK. This is the New Query window:
The Show Table window will pop up next. Click the Close button to begin our “pass through.” After you close the Show Table window your workspace should look similar to this:
Choose Query > SQL Specific > Pass-Through from the Main Menu. Please note that the menu bar in these pictures is on your right-hand side. The default Office installation places this bar on the top of your window.
When your window looks something like the following you are ready to enter “pass-through” SQL:
Creating and Managing a Pass-Through Query
Now we are ready to create a Pass-Through query. In the case of Oracle 7.x we have PL/SQL:
Choose File > Save (or type Ctrl+s) to save this object in the Access database.
Choose View > Datasheet View to run this SELECT query. Before you see any data this ODBC dialog should appear:
In this example the entry smtprod will be used to connect to the Oracle database. Clicking on OK will bring up a second dialog prompting for user name and password. When connection is successful, the display should look like this:
Now that we have a successful connection, let’s try to get some real data. Select View > SQL View to change the SQL to this:
Save the query and return to Datasheet View:
“Advanced” Topics
SQL syntax problems. In truth, Access 97 may have problems with the SQL Syntax of the native DBMS. In the case of Oracle, one must distinguish between PL/SQL and SQL*Plus commands. In the latter case, these commands do not work in Access Pass-Through queries. Namely, the DESCRIBE command does not seem to work passing through Access.
Access appears “frozen” while running the query. Access may take a large time to complete complex queries. While running, Access may report “Not Responding” in the Task Manager and 0% CPU utilization. The recommendation here is to wait and see what happens or kill the Access process and build a “simpler” query.
Saving the ODBC connection string with the query. It is possible to save the ODBC connection string in Access. Doing this will prevent the ODBC dialog from popping up every time the Datasheet View command is executed. By accessing the query properties, this string can be built. However, it is not recommended to save remote DBMS passwords in Access 97. Moreover, using this technique with Oracle databases does not yield successful results.
The information provided by Bryan D. Wilhite at kintespace.com is provided “as is” without warranty of any kind. In no event shall Bryan D. Wilhite or any of his affiliates be liable for any damages whatsoever including, but not limited to, direct, indirect, incidental, consequential, loss of business profits or special damages due to material published by Bryan D. Wilhite or any of his affiliates.