7.1. Pass-Through Queries Versus Linked Tables

One of the easiest ways to access SQL Server data is to link to the table from Microsoft Access and write queries on the linked table. You can link to a table by right-clicking on whitespace anywhere in the database window and selecting Link Tables. The other way to access SQL Server data is through a pass-through query, where query instructions are sent over the network and the query is run on the server with results returned to the client. You choose between ease of use and performance. If you have written queries on Access tables, you know how to write a query on a linked SQL Server table. If the table is relatively small, you gain very little in performance by using a pass-through query. However, if you are dealing a table with several hundred thousand records and you want only records that meet certain criteria, you gain a tremendous advantage by using a pass-through query.

Why? If you write a query on a linked table and set up criteria, each record is brought from the server to the client to be evaluated, resulting in quite a bit of network traffic. If you write a pass-through query, the query instructions are passed to SQL Server and the query is evaluated on the server. Only data that meets the query criteria is passed to the client. The only problem with writing a pass-through query is that you will not get any help from Access in writing it. You need to know SQL Server's own Transact-SQL , though there are some ways around that.

When I have to write a pass-through query with multiple joins, I use a shortcut that helps me write it. First, link the SQL Server tables and use the query designer in Access to create the query. Microsoft Access uses the local table name; for example, a table called dbo.Table1 on SQL Server becomes dbo_Table1 in Access. Once you write your query, go to the top menu, select Query SQL Specific Pass-Through, and the query turns into SQL. Copy the SQL text into a word processor and replace "dbo_ with "dbo," using the Find and Replace function. Next, copy the query text from the word processor and paste it back into Access. Then, go to View Properties, click on the ellipsis on the ODBC Connection String line, and Access takes you through dialog boxes to build the connection string for you.

I have found that even though this adds some steps, it ensures that the joins are correct and in the end is much easier than trying to write SQL by hand. Either method will give you the same results; the only difference will be in performance. The only other item that causes differences is that the wildcard character is "%" in SQL Server and "*" in Access, and there is a similar issue if you run a query from ADO instead of DAO.

There are times when your only option is to use a pass-through query. Let's assume that you want to standardize a parameterized query that you run on multiple occasions. You can do it easily by creating a stored procedure on SQL Server that accepts the arguments and returns the results. The problem with using a stored procedure is that Access cannot link to the table. The easiest way to access the data is to call the stored procedure from a pass-through query. The syntax is very easy. Assume the stored procedure is called Getdata, and it needs two arguments, State and Zip Code. Also, assume that the State argument is a string, while Zip Code is numeric. In this case, the query text is Execute GetData 'PA', 17025; if you create a pass-through query and type in this text, you will get all the records in Pennsylvania with a zip code of 17025. Changing from queries to stored procedures allows you to standardize the method for getting the data and also eliminates errors. This is very helpful in trying to create "one version of the truth" when accessing enterprise data.

Companies often struggle with the decision of how much access to give individual users when accessing data from enterprise databases, provided they have not already bought a tool to do this. The dilemma is that if you allow users to write their own queries for everything, you run the risk of each user interpreting the logic of the data structures differently. For example, a company's Human Resources system gives a unique identifier that increments by 1 for each new record. An employee transfers from Job A (let's say record number 1200) to Job B (record 1250) and then back to Job A. When someone did the data entry, he just activated the Job A record and deactivated the Job B record, giving the employee's current job a lower ID number than her previous job. (It also caused problems with maintaining a history of jobs, but that is another issue.) The problem was identified when the employee did not show up on a query that gave active employees written by one user, but did appear on a different list written by another user. I was asked to look at it and found that one query writer looked at the status of the highest ID number for a particular employee to determine her status, and the other looked at open records. In this case, all we needed to do was standardize the way each report was run and write a business rule for how to handle job changes.

To combat problems like this, write standardized queries and put them into stored procedures that take the arguments needed to produce the desired results. That way, anytime someone needs that data, he can just run the stored procedure, and you can be certain that he is not making query logic errors. For example, you can have a stored procedure that returns active employees, which you can use to get the list anytime. If you need to, you can have the stored procedure create a temporary table that can be linked for other reports. In any case, using standardized views and stored procedures helps cut down on business logic problems.

Stored procedures also help with with security. If you are in charge of a database, you may not want people to have direct access to the tables. Or, if you want to make sure that you don't inadvertently change data, you may not want to access tables directly yourself. One way you can accomplish this is by only giving users access to stored procedures and views (a view is a query on SQL Server). This prevents the users from accessing the tables directly or making any changes to data, except as provided by the stored procedure. For example, you can give a user access to a stored procedure that takes as an argument a unique identifier for a record, and then another argument for the text or numeric data that you want to update a field. This gives the user the ability to update a record by using the stored procedure without giving her direct access to the table. In addition, you can create stored procedures that add new data or append records from one table into another. I certainly recommend this approach if you have multiple users of your database. Also, by using this approach, you can return records to Access without maintaining a constant connection to the database while you page through records. You can have code run anytime data is changed on the client that will fire the stored procedure to make the change on the server. In addition to the performance advantage, you also eliminate the possibility of two people trying to access the same record simultaneously.

Regardless of which method you use on SQL Server, you can accomplish most tasks using pass-through queries from Microsoft Access. If you need to simply page through records, using a linked table is a fine method, but in other cases, I recommend moving as much processing to the server as possible. I should also mention that if you make use of temporary tables (tables proceeded with a # or ##), using a pass-through query is a very easy way to access them.

Wildcards are tricky when you are dealing with Access and SQL Server or using ADO to use data in Microsoft Access. I first encountered a problem when I ran a query in Access that worked but would not return any results when the same query was opened with ADO. I soon found the problem was that the wildcard was * (Access/DAO wildcard) instead of % (ADO/SQL Server wildcard).

When fixing it, you run into the problem that if you set it up with % to run with ADO, you will not be able to open it in the Access GUI. You will run into the same problem if you write the query in the Access GUI and convert it into a pass-through query. Just keep in mind that if you are using wildcards to make sure that when you use DAO or the Access GUI, use * (even if the data is on SQL Server), and use % with ADO and SQL Server Pass-Through queries.