What is a Pass-through Query?
A Pass-through query allows you to execute an SQL statement directly against the tables in an external database (such as an Oracle, Sybase, or SQL Server database). This method uses your ODBC connection to directly access the data in the external database without having to link to the tables or import the data from the external database.
This tutorial will demonstrate how to create pass-through queries as well as how to integrate pass-through queries with other queries in your Access database.
Create an ODBC connection
The first step is to create an ODBC connection to your external database. This ODBC connection will be used by the pass-through query to access the tables directly in your external database.
If you don't already have an ODBC connection defined, you can create one by selecting Data Sources (ODBC) under the Control Panel.
When the ODBC Data Source Administrator window appears, select the User DSN tab. Then click on the Add button.
When the Create New Data Source window appears, select the ODBC driver that you wish to use. In this example, we've selected the driver called "Microsoft ODBC for Oracle". Click on the Finish button.
Next, enter the Data Source Name, Description, User Name, and Server for the ODBC connection. Then click on the OK button.
Now, when you return to the ODBC Data Source Administrator window, you should see the ODBC connection that you've just created.
Create a Pass-through Query
Now that you've set up your ODBC connection, you can begin to setup your pass-through query.
To do this, create a new Query. When the New Query window appears, select Design View.
When the Show Table window appears, click on the Close button.
You should now have a blank query that looks like the one below.
Write SQL for your Pass-through Query
Since you are choosing to create a pass-through query instead of linking to the table, Access does not know the table or field names in your external database. So you will have to write your SQL without the help of the Query Builder in Access.
As a result, you'll need to switch your query to the SQL View. To do this, select SQL View from the Toolbar. See picture below.
Your query should now look like this.
Now, type in your SQL statement.
In this example, we've entered the following SQL statement:
SELECT supplier_id, supplier_name, address_line1, address_line2, city, state
FROM suppliers
Now, you'll notice that we left out the semicolon at the end of the SQL statement. Some of the external databases, do not like the SQL statement terminated with a semicolon, so you may have to try your SQL statement with a semicolon and without a semicolon, to see which it accepts as proper syntax.
Tag the query as a Pass-through Query
Now that you've created your SQL, you need to let Access know that you intend to use this query as a pass-through query.
To do this, select SQL Specific > Pass-Through under the Query menu.
Your query should now display "Pass-Through Query" in the title bar on the Query window. See below.
Link the Query to the ODBC connection
Now, it's time to link the query to the ODBC connection that you configured at the beginning of this tutorial.
To do this, under the View menu, select Properties.
When the Query Properties window appears, click on the property called "ODBC Connect Str". A button with 3 dots to the right of the property should appear. Click on this button.
When the "Select Data Source" window appears, highlight your ODBC connection that you configured at the beginning of this tutorial. Then click on the OK button.
When the ODBC login window appears, enter your password and click on the OK button.
You will be asked if you wish to save the password in the connection string. Since the connection string is visible (and not protected) within your Access database, we recommend you not save your password for security reasons. In our example, we will select No to this prompt.
Now when we return to the Query Properties window, you can see the ODBC connection string that you've just built. (Note that the password is not contained in this string, so you will be prompted to enter a password when you run your pass-through query.)
Run the Pass-through Query
Now that we've configured our pass-through query, let's try running it.
You can execute the query by selecting the Run button on the Toolbar.
Below are the results from our pass-through query.
Now, let's take a look at how we can integrate our pass-through query with our other Access queries.
Integrating a Pass-through Query with a Make-Table Query
One of the limitations of using a pass-through query is that it can only access data from your external database. So if you need to move the data from the external database into your Access database, you can create a Make-Table query that uses the pass-through query as input.
Let's take a look at an example.
First, create a new Query. When the Show Table window appears, click on the Queries tab. Then select the pass-through query that you just created. In this example, our pass-through query is called Query1. Then click on the Add button.
Next, select the fields from Query1.
Under the Query menu, select Make-Table Query.
When the Make Table window appears, enter the name of the table that you wish to create. Click on the OK button.
Now, when you run your Make-Table query, your new table will show in the Database window and it will be populated with data from your external database.
Integrating a Pass-through Query with an Append Query
If your table already exists in Access, but you need to move the data from the external database into this table, you can create an Append query that uses the pass-through query as input.
Let's take a look at an example.
First, create a new Query. When the Show Table window appears, click on the Queries tab. Then select the pass-through query that you just created. In this example, our pass-through query is called Query1. Then click on the Add button.
Next, select the fields from Query1.
Under the Query menu, select Append Query.
When the Append window appears, enter the name of the table that you wish to append records to. Click on the OK button.
Your Append query should now look like this.
Now, when you run your Append query, your table will be populated with data from your external database.
Congratulations! You've now:
- Created an ODBC connection
- Configured your first pass-through query
- Learned how to integrate a pass-through query with other queries in Access.
