How do Access queries work?

[jd]

I have a question on how Access queries work. I have a .mdb and all tables
are linked to a SQL backend. When I create a query and add criteria, does
Access bring all the data from that table to the client and then applies the
criteria or is the data filtered on the server and only the data requested
by the query sent to the client?

Thank you

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and has nothing to do with ODBC linked
tables and passthrough queries. In your case, the best way of seeing what
happens on the server is to use the SQL-Server Profiler; it will show you
all requests made by Access.

If you do so, you will see that for queries that are not to complex, that
don't use VBA functions, etc., that Access sent the whole criteria to
SQL-Server to retrieve the primary keys. It then uses these primary keys to
retrieve the other columns for each individual tables by group of ten rows;
so a query that need to retrieve 100 rows on two tables in a 1-1
relationship should take 21 requests to the SQL-Server if I remember
correctly. However, you should verify this fact by yourself because it has
been many years since the last time that I've checked that.

For more complex queries, Access will ask the SQL-Server to return all rows
and it will perform the criteria itself. Needless to say that the
performance will drop faster than a rock in these cases. You should verify
all of your queries with SQL-Server Profiler to see that this doesn't happen
to your system.