pass through

[deb]

From reading in this group it looks like I may need a pass through query.

Can you tell me what it is and how to create one.

I have an ODBC connection to a table. I did File > Get External Data > Link
Tables.
It created a table called b_Exports. When I use this table. It is S L O W,
to say the least.
I do not need all record in the b_Exports table. I only need the ones that
match the MOrder field from tMasterData table.

What should I do and how do I do it.
Thanks in advance for your detailed explination for me.

 

[Armen Stein]

From reading in this group it looks like I may need a pass through query.

Can you tell me what it is and how to create one.

I have an ODBC connection to a table. I did File > Get External Data > Link
Tables.
It created a table called b_Exports. When I use this table. It is S L O W,
to say the least.
I do not need all record in the b_Exports table. I only need the ones that
match the MOrder field from tMasterData table.

What should I do and how do I do it.
Thanks in advance for your detailed explination for me.

Hi Deb,

In Access 2003, a passthrough query is specified in the Query Designer
by selecting SQL Specific - Passthrough. It might be a bit different
if you use a different version, but it's there. You need to set the
connection string to the server yourself. The query is passed
directly to the server - Access doesn't interpret the query.

For more on passthrough queries, check out my PowerPoint presentation
on techniques for using Access as a client-server front-end to SQL
Server databases. It's called "Best of Both Worlds" at
www.JStreetTech.com/Downloads. It also includes some thoughts on when
to use SQL Server, performance and security considerations,
concurrency approaches, and techniques to help everything run
smoothly.

If your join includes both a passthrough and a local table, it will be
slower because the query will be "heterogeneous" and will force Access
to do some of the work. It might be faster to append all the data to
a local work table, then perform all your processing locally.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom]

I'm going to "hijack" the thread with a similar question.

Same setup deb described - linked tables via ODBC. All forms have a
(regular) query as a recordsource. I have continuous forms that
displays a limited subset of fields for a large number of records to
allow the user to find the record he wants. A doubleclick opens a
detail record with just one record retrieved.

Perfomance is so slow as to be only marginally useful.

Will I notice a significant improvement if I ditch the linked tables
and replace with a pass thru query - especially on the continuous
forms? Or is the number of records I am retrieving the main driver?

Thanks - and apologies to deb for the hijack, but hopefully you will
benefit from the answer too!

Tom

 

[Armen Stein]

I'm going to "hijack" the thread with a similar question.

Same setup deb described - linked tables via ODBC. All forms have a
(regular) query as a recordsource. I have continuous forms that
displays a limited subset of fields for a large number of records to
allow the user to find the record he wants. A doubleclick opens a
detail record with just one record retrieved.

Perfomance is so slow as to be only marginally useful.

Will I notice a significant improvement if I ditch the linked tables
and replace with a pass thru query - especially on the continuous
forms? Or is the number of records I am retrieving the main driver?

Thanks - and apologies to deb for the hijack, but hopefully you will
benefit from the answer too!

Tom

Hi Tom,

I believe you're describing the typical read-only "index" form, which
allows you to sort and filter the records you're interested in. Then
you drill down to open the single "detail" record for editing.

If that's correct, then yes, you can definitely see performance
improvements by basing the index form on a passthrough query,
especially with the NOLOCK hint. This is especially true if you have
any calculation columns or complex selection criteria have would force
Access to do the work instead of handing it over to the server.

You still open the detail form, bound to the linked table or query, to
retrieve only 1 record for editing using the WhereCondition.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom]

I believe you're describing the typical read-only "index" form...

That is correct. I'll give pass throughs a try - thanks

Tom