Problem
Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name (DSN) using the OLEDB driver, created a System DSN for the new SNAC (SQL Native Client) driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment! There has to be a better way!
Solution
Of course there is. Microsoft Access gives you the ability to issue the query created in Access directly against the SQL Server database, using T-SQL commands. This is called a Pass-Through Query in Microsoft Access. By the time you have finished reading this tip you will know precisely how to create one.
If you have been following along with this tip series on Microsoft Access and SQL Server integration then you should have an Access database with the following linked tables to an instance on your SQL Server for the 2005 Northwind database. The ‘Tables’ listing should look something like what you see below.
Let’s take a minute to create a basic Access query against the dbo_Orders, dbo_Customers, and dbo_Employees tables first. Select ‘Queries’ under the ‘Objects’ menu on the left side of the main Access interface. You should see a form similar to what is displayed below. Select the ‘Create query in Design view’ option on the right pane of the window.
Once the ‘Show Table’ interface displays, then select the three tables: dbo_Orders, dbo_Customers, and dbo_Employees. Next, click the ‘Add’ button to begin designing the query.
What you will see next is a query screen that looks something like what is presented below. I have altered the tables’ graphical placement and size for presentation purposes, but your screen should be similar. The Access Query Designer is very similar to the one you find incorporated into SQL Server.
Double-click the fields as follows in order to load them into the query:
dbo_Orders
- OrderID
- ShipCountry
dbo_Customers
- CompanyName
dbo_Employees
- LastName
- FirstName
Select to sort the results by ‘Company Name’ and to limit the results to only those where the Ship Country is ‘USA’. The Access Query Designer should look something like this:
Now if you run the query as-is (via either selecting Query | Run from the menu bar or by clicking on the 
button the following results are displayed.
So, you may ask, “What is wrong with this query? Well, it all comes down to what I mentioned earlier: Access will load the entire data set prior to filtering the data and returning the results. Using our example, this means that the complete Orders, Customers, and Employees tables will be returned from SQL Server into Access. Afterwards, the tables will be joined and the query’s WHERE clause will be applied, limiting the results to only those records where Orders.ShipCountry = “USA”. Furthermore, Access is doing all the processing. All the processing power of your SQL server is disregarded – the client workstation where Access is being run is bearing the brunt of all processing.
Microsoft Access Pass-Through Queries
This is where the Pass-Through query comes into play. Let’s first take a look at how to convert this query into a pass-through query first and then I will explain what this accomplishes.
To convert the query into a pass-through query, select Query | SQL Specific | Pass-Through from the menu bar. In doing so you will see that the query view switches to SQL text. Once you convert the query into a pass-through query, design view is not available. This should be familiar ground though for the experienced or novice SQL Server DBA.
The query text does look a little different from what you’re accustomed to. Try running it and see what happens:
So, what is wrong? Well if you convert an existing Access query into a pass-through query, it will use the proprietary Access SQL language, not T-SQL. This is an issue, since we are “passing” the query back to the SQL Server for parsing and processing. This means that the query must be written in the T-SQL language. Therefore you have two options: convert the SQL Server code to T-SQL or create the query as a pass-through query from the start.
- Converting the SQL Server code from Access to T-SQL typically comes down to replacing the underscore from the table name with a period (dbo_Orders in our example will become dbo.Orders) and replacing double-quotes, signifying a text value, to single-quotes.
- Creating the query as a pass-through from inception simply means that you select Query | SQL Specific | Pass-Through from the menu bar prior to adding tables to the query while in design view.
What I tend to do is build the query I want to run in SQL Server Management Studio first. I then paste the query text into the Access pass-through query. This resolves two dilemmas: it allows reviewing the estimated execution plan, and it confirms that the query will run successfully. Below I’ve done just that. I’ve converted the existing query into T-SQL.
The results are the same, but I’ve accomplished two things. I’ve forced the query to execute on the SQL Server, not the client workstation. I’ve also eliminated the Access default process of loading the complete record set into the workstation prior to processing the query. I can now save this query and use it as the record source for an Access form or report. Essentially, I can use this query as if it is native to the Access database.
Next Steps
- Review the other tips in this series on SQL Server interaction with Microsoft Access. These include:
- Check out all of the Microsoft Office Integration tips
- Using your own data, create a report or form in Access to display SQL Server data via a pass-through query.
