I am trying to understand how the Access Database Engine, the Jet, uses linked tables.
Some background . . . and if my understanding is incorrect, please correct me . . .
A part of the Access executable (msaccess.exe and its associated DLLs) is the Jet and it operates against tables and queries stored in Access mdb/e(s) and accdb/e(s).
If you have an application separated into an Access user interface front end and an Access data repository backend, with tables linked between the two, the Jet is able to use data in the backend as if the table/query objects resided in a single mdb/e or accdb/e.
Now, if I have an application separated into an Access user interface front end and a SQL Server data repository backend, with tables linked between the two, I believe that there is a significant difference in how data from the SQL Server is accessed.
My understanding, and it may be wrong, is that if Access must run a query, it must pull the entirety of data from every SQL Server table in that query onto the machine running the Access executable in order to allow the Jet to process that data.
So, for the sake of discussion, if an Access query is linked to 10 tables, each with 100,000 rows of data, the Jet must process 1,000,000 rows of data regardless of the number of rows ultimately resulting from the query. And this is because the Jet is the database engine which is processing the data, not the SQL Server Database Engine. If, in this example, the expected result was the return of only five rows of data, this attachment paradigm would mean that you would be tying up your network transfering 1,000,000 rows of data in order to throw away 99.9995% of that data.
If one were to write that same query as a view or stored procedure which was called from an Access front end, but that would actually be processed on the SQL Server (by the SQL Server Database Engine), then the only data being transferred across the network would be the five resulting records.
I am asking this because I have a customer who has a home-grown Access application (with tables linked to a SQL backend) with a report that requires 40 minutes to run. The report is an absolute mess and the application uses SQL Server simply as a "table server," not taking advantage of any SQL Server capabilities besides table storage. Fundamental to this mess is the fact that the report contains about a dozen subreports, and the report itself and each of the subreports use a record source query containing a table with 600,000+ records of 50+ fields of nvarchar(50). I don't think the Jet is smart enough to know that each subreport is using a common table, so I am assuming that the Jet is pulling the entirety of this table across the network 13 times.
I just want to clearly understand how the various database engines work with linked tables.
Am I correct is my understanding as to how this all works?
Thank you for your comments.