Pass Through and Data Definition queries 1

[puforee]

I had a chance to use my first SQL UNION query this morning. I used a search on this site to learn how to use it.

This brings two questions to mind:

1. What is a pass-through query and what is it used for?
2. What is a data definition query and what is it used for?

I did a search on this site but I did not get a clear understanding what they would be used for.

Thanks,

 

[kjv1611]

For #1:

http://www.techonthenet.com/access/tutorials/passthrough/basics01.php

Basically, a passthrough query allows you to run external SQL against external databases via ODBC connections, rather than linking in the data sources, and then running queries on the linked tables.

When you build a passthrough query, you have to follow the rules for the host SQL database, not jetSQL, which is what MS Access standard queries use.

 

[kjv1611]

Here's a reference on data definition:

http://islandman.org/umuc/ifsm410/DataDefinitionQuery.html

 

[puforee]

kjv1611,

Thanks for the information. I do linke to Oracle databases to compile data for reporting (read only) Would the pass through work for Oracle DB's or ????

Also, is their an advantage to using pass-through queries verses linked tables?

I will read your reference on the data definition queries as soon as I get a chance. Thanks for this also.

 

[kjv1611]

Yes, they definitely work for Oracle. You just have to know the correct Oracle Syntax in the passthrough queries.

Also, something I've learned the hard way: with passthrough queries, it's one query per passthrough query object. Whereas in Oracle or MS SQL, you can have multiple queries back to back in the same sheet.

 

[kjv1611]

Yes, they definitely work for Oracle. You just have to know the correct Oracle Syntax in the passthrough queries.

Also, something I've learned the hard way: with passthrough queries, it's one query per passthrough query object. Whereas in Oracle or MS SQL, you can have multiple queries back to back in the same sheet.

As to this question: Also, is their an advantage to using pass-through queries verses linked tables?

Yes, pass through queries will almost always return results more quickly. And here's why:

With a linked table, you're basically reading each table in its entirety (well not exactly, but that's the basic idea) across your network onto your machine, and then running the query. So if each table has millions of records, you're pulling all that in and running the query locally. When you run a passthrough query, you basically send the instructions to the server, tell it what you want, and it sends just the results to you. So the performance can be MUCH better with PTQueries at times. Not to mention it should greatly reduce network bandwidth usage.

 

[puforee]

kjv1611

Thank you very much for the informaion. I will be working very closly with my dba to enhance my information gathering.

Great explaination.

:-D