Passthrough queries

[Terje Martinsen]

Using Access 2003 (forms, queries, reports, macros).
Data tables on SQL Server 2000, tables linked to Access "database".

A) Can anyone please explain the benefits of making passthrough queries
instead of "normal" queries.
B) Are there any noticeable drawbacks?

Best regards
Terje Martinsen

 

[Marty]

If you are doing a select query then, for the most part,
a normal query will do just fine. If you are doing an
update, append, or delete query it makes all the
difference in the world. It depends on how the MSSQL DB
was created, but there are fields and tables that cannot
be changed through the link in ACCESS. The only way you
can make changes is with a Pass Through Query.

One of the negatives of having to use a pass through
query is that you must have knowledge of SQL syntax. You
can on occasion use the standard query GUI then "turn" it
into to SQL syntax by selecting the query type as Pass
Through. This will open a new window with the SQL
syntax. But you're not going to get away that easy.
Sometimes the code is not entirely correct for the Pass
Through protocol. It's usually just a minor change
though. The only other negative I can think of is that,
since the changes in the MSSQL DB are realized real time
and there's no un-do, you can seriously screw-up the SQL
database. Getting it back, depending on what the
database does, could cost your company serious money in
downtime. A work around for this is to have a "mirrored"
or copied database to test your procedures, and then run
it on the "real" database after you confirm all is well.
If it's just select queries and you're creating reports
none of the negatives apply. Use the ACCESS interface.

Marty

 

[Rick Brandt]

Using Access 2003 (forms, queries, reports, macros).
Data tables on SQL Server 2000, tables linked to Access "database".

A) Can anyone please explain the benefits of making passthrough queries
instead of "normal" queries.
B) Are there any noticeable drawbacks?

Local Access queries against ODBC links can have a wide range of processing
possibilities. Some will have the SQL passed to the server and all of the
processing will be done there with only the result passed back to your app.
Others will cause large blocks of data (perhaps entire tables) to be sent
to the client PC where the query is processed locally. Also possible is a
split of the processing anywhere between those two extremes.

While most standard queries against ODBC linked tables will be processed
primarily on the server, using Pass-Throughs and Stored Procedures is a way
to guarantee that 100% of the processing will be server-side. Given the
above you can see that changing many queries into PTs or SPs will have
minimal benefit. My advice is always to build the query first in the
manner that is fastest and easiest for you to get it right and then see how
it performs. If you feel that perhaps it could be a little faster you can
create a duplicate as a PT or SP and see if there is any benefit.

I would also point out that with the hardware level of most modern PCs
getting server-side processing is not nearly as important as it once was.
What _is_ still important is limiting the amount of data sent back and
forth over the network. The same practices that attempt to get more
processing on the server usually result in less data traffic as well.

 

[Terje Martinsen]

Thank you for your excellent reply!

regards Terje