Integrating Excel and Access by Michael Schmalz

Author:Michael Schmalz [Schmalz, Michael]
Language: eng
Format: epub
Tags: Business, Database Management, Electronic books, Data Modeling & Design, Microsoft Excel (Computer file), Computers, Spreadsheets, General, Desktop Applications, Document Management, Databases, Data Processing, Computer programs, Microsoft Access
ISBN: 9780596009731
Publisher: O'Reilly Media, Inc.
Published: 2006-04-15T07:00:00+00:00

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.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.