Linked Databases and Pass-Through Queries

[Jim_Callahan]

Pass through queries can be used to define linked data bases, but not all linked data bases require pass through queries. An MS Access database linking to another MS Access database is one example that would not require a pass-through query.

Pass through queries are useful when you want to either:
1. Take advantage of non-standard (or non-SQL) query syntax on the linked to database.

Examples:
a. Using an IBM Db2 specific SQL syntax
b Linking to an MS Anaysis Services (multi-dimensional OLAP cube with non-SQL syntax)

or 2. take advantage of summarization on the linked to data base.

Larry Jones wrote:
> The definition of a Pass-Through query (For Access) is:

> "An SQL-specific query you use to send commands
> directly to an ODBC database server (such as Microsoft
> SQL Server). By using pass-through queries, you work
> directly with the tables on the server instead of having the
> Microsoft Jet database engine process the data."


> Given that definition, do you really need to use a Pass-
> Through query as opposed to regular SQL queries?

> 1. I doubt that the ODA ODBC driver supports pass-through

YES, EXCELLENT POINT! Support of pass-through queries by ODA would raise data security issues (is ODA still "read only" since one might be able to pass-through a data altering query) and by definition ODA would not be able to intercept SQL calls and convert dates and apply decimal places based on the data dictionary. In other words supporting pass through queries would defeat the 3 main purposes of ODA!

> 2. If you really need pass-through capability, then you want
> to use the ODBC/OLEDB driver for your actual database -
> not ODA

Given the above, yes, I would have to agree.

One option would be to have MS SQL Server do the query and have present a safe, read-only view to the MS Access client.

HTH,
Jim Callahan

 

[Jim_Callahan]

Correction

My post is NOT accurate for MS Access. I confused some features of MS SQL's "Linked Servers" with MS Access' "Linked Databases." The two have related functionality, but are very different.

In MS Access "Links" are created in the "File" menu:
FILE > GET EXTERNAL DATA > LINK DATA


In MS Access "Pass-through Queries" are created in new query area after closing the "Add Table" dialogue (which would seem to preclude the use of a linked data base).

Both linked tables and pass-through queries use ODBC, but it is not clear whether MS Access allows them can be used in combination. The help file refers to "pass-through queries" as an alternative to "linking."

"Chapter 5: Linking to SQL Server databases" in Chipman & Baron's "Microsoft Acess Developer's Guide to SQL Server" provides a good discussion of MS Access Linked Data Bases and pass-through queries.

A separate issue is that MS SQL Server provides a feature called "Linked Servers." I had confused some of the features of MS Access "Linked Databases", pass-through queries and MS SQL Server "linked Servers." On SQL Server the OPENQUERY function (which provides pass-through capability) is closesly tied in with the Linked Server functionality. Page 391 of Chipman & Barron, "OPENQUERY works with a linked server to allow you to process a query on the linked server. This allows you to use SQL syntax that is not supported in Transact SQL." [Snip] "OPENQUERY only works with linked servers and exists soley to provide extended functionality that exists on your linked server but that isn't available in [MS] SQL Server."

I implicitly and erroneously assumed that MS Access Linked databases worked the same way as MS SQL Linked Servers.

Again the Chipman and Barron book has an excellent chapter, "Chapter 8: Introduction to Transact SQL (T-SQL)" and in particular pages 385-393 -- the section begins, "Creating Distributed Queries with Linked Servers."

BTW, Chipman & Barron is also the source for querying Analysis Services using a Pass-Through query from MS SQL SERVER -- see pages 637-654.

Although Microsoft's product line is confusing, I should have
double checked before I posted. Sorry for the confusion.

Jim Callahan