"Using a Pass Through query requires some SQL knowledge but is a very powerful way to collect data from an external database without using the conventional ODBC link tables..."
Pass Through Query allows you to execute an SQL statement directly to tables (or other source files) to an external database such as SQL Server and Oracle.
This method uses the ODBC (Object Database Connectivity) connection to directly access the data in the external database without having to link to the tables/source files or import the data from the external database.
The example below shows you how to create an ODBC connection to an external database followed by the creation of a Pass Through query connecting to the ODBC database.
Note: You will need to know the SQL language (syntax) to create this type of query.
Pass Through query example
This example query will connect to a Sage (Line 50 v12) database system which is an accounting system and will call all companies listed in the sales ledger of this system.
I'm using Access 2007...
Step 1: You first need to create or check the ODBC connection object to the external database is set and ready.
You will normally need to know the path, username and password to connect to this type of object and configure it via the Data Source (ODBC) utility which can be found in the Control Panel of your Windows operating system.
Click on the User DSN tab and to create a new ODBC connection, click the Add... button.
Step 2: The Create New Data Source dialog box appears where you scroll down for the correct driver.
In my example, I want to choose Sage Line 50 v12 and then click the Finish button.
Depending which driver you choose a different screen will appear for that driver and in my example the following screen appears:
Close the main ODBC Data Source window - ODBC is now ready!
Step 3: Back in Microsoft Access you can now start to build the Pass Through query.
Create a new query by locating the Create tab from the Ribbon bar clicking on the Query Design icon from the Other section.
As in a normal new query, you see the Show Table pop-up window which can be closed (as it's not required).
From the Ribbon bar, you will see the Design tab and various icons in the Query Type section where you click on the Pass-Through icon.
The main view switches from the design grid (QBE grid) to the SQL only view.
Step 4: You now set the ODBC to this query by setting the ODBC Connect Str property which can be found in the Property Sheet pane.
This window is located in the Design tab on the Ribbon bar in Show/Hide section.
Click on the button in this property (located to the right) which displays the Select Data Source window.
Choose the data source name driver from the Machine Data Source tab and click the OK button.
Once again, depending on the driver chosen a different pop-up window appears requiring (as in most cases) a login and password to open the database connection.
You will also see a prompt to save the password entered so that the next time the system will automatically log you into the database.
Note: Consider whether you should allow the system to remember passwords for future use. Sensitive data may require the added security of users being prompted each time to enter a valid password as opposed to letting the system default with the known password should an unauthorised user run a query of this type.
The ODBC Connect Str is filled out and my example will look something like:
The query now has a connection.
Step 5: Now you treat this as any normal query with the exception that you need to write SQL instead of being able to set the query via the QBE grid interface.
In my example, I add the following SQL which returns all customers from the sales ledger of the Sage accounting system.
SELECT ACCOUNT_REF, NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, CONTACT_NAME, TELEPHONE, FAX, E_MAIL, WEB_ADDRESS, VAT_REG_NUMBER, CREDIT_LIMIT, ACCOUNT_ON_HOLD, TERMS, DATE_ACCOUNT_OPENED, FROM SALES_LEDGER SALES_LEDGER;
Run and save the query - Job done!
This query can now be used with other queries, reports, forms and treated as any normal object in Access.
I would recommend that you use this type of query to generate a Make Table query or use it to add records using the Append query to copy the data into a local table so it becomes an offline recordset and disconnects from the server side database.
Note: You cannot use an Access database or Excel spreadsheet driver for this type of query connection even there are ODBC drivers for these applications.
Finally, most ODBC connections are read only which means the data cannot be edited and updated.
Return from Pass Through query to Access Queries
Return from Pass Through query to About Access Databases home page