How does MS Access join linked tables (linked to the same SQL server database)?

Question

I've got MS Access database with linked tables, whenever each table is linked to a table in the same SQL Server database. I have a query inside Access that joins two tables (in particular I'm updating a table based on another using a join).

The question is does Access "download" all the table data before doing a join? Or is smart and joining it on the SQL Server?

The query is:

UPDATE TBL_INVOICE_CHARGES INNER JOIN TBL_ANI 
ON  (TBL_INVOICE_CHARGES.CH_CUST_ID = TBL_ANI.ANI_CUST_ID) 
AND (TBL_INVOICE_CHARGES.CH_ANI = TBL_ANI.ANI_NZ_ANI) 
SET TBL_INVOICE_CHARGES.ANI_NOTES = TBL_ANI.ANI_NOTES;

Answer 1

According to Albert D. Kallal (Access MVP), Access does not pull the whole tables, except for complex views, where things can get messed up. However, it is best to use views on the server and link to the view, or to use pass-though queries.

Further information: How to create an SQL pass-through query in Access

Answer 2

Likely Jet will hand off the whole thing to SQL Server, which will do an index join and then perform the updates. In other words, for a simple query like your example, it's all being done on the server, with not one byte pulled across the wire for local processing.

It's pretty easy to make Jet pull the whole table. The easiest way is to put an Access expression in your WHERE clause. Here's an example that would cause it to happen:

   WHERE Format(MyDate,"YYYY") = 2008

The whole table is going to have to be pulled so that Access can run the Format() function on all the dates in your table. Also, it won't be able to use any indexes and will thus be very slow. It would be slow with a Jet back end, too, simply because it's so inefficient. The proper way to write this WHERE clause is:

   WHERE MyDate Between #1/1/2008# And #12/31/2008#

If you write that in a saved Access query, it will be handed off to the SQL Server for processing (and ODBC will send appropriate delimiters if your back end db engine uses different ones than what Jet SQL uses).

But if you're not doing that kind of thing, you're unlikely to encounter the problem of pulling too much data across the wire. Jet is, in fact, quite smart, and does a remarkably good job of sending as much of a query across the wire for processing as possible. For example, if you call Access functions in your SELECT statement, the underlying select without the Access functions will be sent to the server, and the functions will then be performed in Access on the result set. For this Access query:

   SELECT Format(MyDate,"MM-DD")
   FROM MyTable
   WHERE MyDate Between #1/1/2008# And #12/31/2008#

Jet will send this to the server:

   SELECT MyDate
   FROM MyTable
   WHERE MyDate Between #1/1/2008# And #12/31/2008#

Once Jet has received from the server only the rows that match the criteria, it will only then format the date field using the Access Format() function. This also works with JOINs, especially joins on indexed fields (though non-indexed field joins will probably also be handed off to the server).

Now, sometimes Jet does guess wrong and ends up being incredibly inefficient. In those cases, you can set up views and stored procedures on the server and use passthrough queries to make sure that Jet's wrong guesses will be avoided.

Answer 3

I've looked at SQL Profiler (unfortunately i don't have the actual log handy) and here's what i found:

• it selects all records from both tables
• for each record in one table calls an UPDATE So this will be slow.