If what you're describing in a scenario where each SQL Server table is added to the MS Access project as a linked table, and then you're joining linked tables together on the client side using MS Access SQL, then that's problematic for performance. This may appear like a normal simple SQL query in MS Access, but it's actually a distributed query that results in pulling across the network more rows than needed to the client, because it's joining and filtering locally within the application.

The following article describes how to leverage views, stored procedures, and pass-through queries to more efficient query from SQL Server. Essentially the goal is to run the SQL query remotely on the server, thus performing all joins, filtering, sorts, etc. server-side, and then return only the final resultset back to the client-side application.

Optimizing Microsoft Office Access Applications Linked to SQL Server

https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx