Access ODBC: How does it handle queries?

[Zach]

Can someone please tell me how Access 2003 handles regular queries to
Oracle servers?

Does Access hand off the SQL to be executed on the Oracle server, or
does it download the needed table(s) and have Jet execute the SQL on
the client?

For background, I'm talking about an Access DB, not an Access Project.
I'm also meaning regular queries, not SQL-Specific Passthroughs.

Thanks!

 

[Rick Brandt]

Can someone please tell me how Access 2003 handles regular queries to
Oracle servers?

Does Access hand off the SQL to be executed on the Oracle server, or
does it download the needed table(s) and have Jet execute the SQL on
the client?

Yes and Yes.

In other words it will do both depending on the query. A passthrough query
will be sent entirely to the server for processing. A standard query
against an ODBC link will send anywhere from all of the work to the server
to almost none depending on how the query is constructed.

My general advice is to use an Access query against the link and see how it
performs. If performance is good then you can leave it that way. Queries
that join multiple links from the same server would likely benefit from
building a view on the server that does the join and then just query the
view. That depends on whether you need the query to be editable though.

The important thing is to try to get the WHERE clause and the joins done on
the server whenever possible. That minimizes the data that needs to go over
the LAN.

 

[Pieter Wijnen]

Access offloads as much work as it can to Oracle

Pieter

 

[Pieter Wijnen]

Access offloads as much work as it can to Oracle

Pieter

Can someone please tell me how Access 2003 handles regular queries to
Oracle servers?

Does Access hand off the SQL to be executed on the Oracle server, or
does it download the needed table(s) and have Jet execute the SQL on
the client?

For background, I'm talking about an Access DB, not an Access Project.
I'm also meaning regular queries, not SQL-Specific Passthroughs.

Thanks!

--