Your general application architechure (Access with Linked Tables) is probably flawed for this purpose. It can probably be fixed (mostly) but not without some revision.
The key issue is, that when Access is the front end, Access itself determines whether it can submit the entire query for processing by SQLServer, and often comes to the conclusion that it cannot, so it often performs the query itself, by downloading the whole table. This is particularly true with Joins, especially if there is not a strong primary key on each of the tables in use.
Here is the MS Technet doc on performance for Access Linked Tables with SQLServer backend: https://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx
The most pertinent bit is:
The major cause of query performance degradation is when a query
involving very large tables requires that all of the data from one or
more tables be downloaded to the client. This can happen even when
joins or criteria appear to limit the result set to a small number of
records. This occurs because sometimes the Office Access database
engine determines that it cannot submit an entire query to SQL Server.
Instead, it submits multiple queries, often including queries that
request all of the rows in a table, and then it combines or filters
the data on the client. If the criteria require local processing, even
queries that should return only selected rows from a single table can
require that all the rows in the table be returned.
- Using the JET/ODBC diagnostics utilities to determine where all the
work is being done.
- Using Snapshot RecordSets in access where possible.
- Push query logic to the SQLServer with Views and Stored Procedures/Functions.
- Establish solid Keys and Indexes in SQLServer, and make sure the indexes are defragmented as needed.
- Caching commonly used data in Access.
The link above has lots of great advice and technical details for someone in your position. Good luck!
