When joining linked tables, even tables with the same data source, Access will not structure and optimize the query exactly the way SQL Server would.  The net result is usually that Access will try to pull a lot of extra data over locally and operate on it there instead of letting the server do the work.

A good general rule of thumb is to avoid joining linked tables in an Access query whenever possible, and use pass-through queries instead... that way you know for sure the server will be doing the work, and network and client overhead will be kept to a minimum.

For more details on all of this note the last 2 major bullets in this KB article:  http://support.microsoft.com/?kbid=286222

Also note the mention of how to utilize a "remote index join", which can come in very handy for certain situations.

Greg Gonzalez

sqlSentry