Results 1 to 5 of 5
  1. #1
    PracticalProgram is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    5

    Questions as to how the Access Jet operates with linked tables . . .

    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.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm only going to answer part of this.
    The Jet database engine was in use before A2007 when it was replaced by the more efficient ACE.
    It is a common misconception that Access needs to download all records to run a query.
    Query optimisation means Access handles data far more efficiently.
    Using stored procedures or views MAY be more efficient still but in many situations, the difference will be negligible.

    However if a report takes 40 minutes to run, there is clearly a huge issue with its design.
    If it uses 10 linked tables, then I'm not surprised if it's slow...though not that slow
    Check the record source used. For example, are all fields used in grouping, filtering or sorting indexed?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.
    No, that is incorrect, although a badly designed query may require it.

    The ODBC driver (used when you connect to an external table) first will translate what it can ofJET/ACE sql into TSQL (in the case of sql server) so that sql server can run the query in its own environment and return the resultant dataset. However there are occasions when it can't. For example if the query uses a mixture of tables from sql server and the local db. Or if the query uses vba functions (which sql server won't recognise) such as the domain functions, nz, date and the like. There can also be issues around using subqueries as criteria.

    Other factors that can affect performance are non normalised tables/relationships, lack of indexing etc, not to mention a slow network.




  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.
    Sounds possible. Are you suggesting that this whole thing boils down to 5 records?
    You seem to understand SQL Server, so you could mock up something with the data and your thoughts and give it a try.
    I'm sure it would be a great sample for the KnowledgeBase on this forum.

    I am not a SQL Server person, but have used Access with pass through queries on Oracle to improve performance (many years ago).

    Regardless, it seems that design may be a suspect in the performance issue; but as others have said --there are a number of
    culprits to be interrogated.

    Good luck. If you do get it resolved, please consider posting your solution in the forum.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Just to chime in, the quickest route to see if it's Access formatting the report bogging things down, would probably be to create SQL view(s) mimicking the reports recordsoucre queries, and connect those to the report.

    As others have said it could be caused by a lot of different factors. JET or ACE / ODBC will handle it very efficiently if the queries are written correctly.

    Edit - have a read here : https://technet.microsoft.com/en-us/...v=sql.90).aspx
    Last edited by Minty; 04-20-2018 at 04:51 AM. Reason: Added link
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 9
    Last Post: 02-19-2015, 06:50 PM
  4. Does MS-Access's Linked tables are supports on PHP
    By rajendradewani in forum Programming
    Replies: 2
    Last Post: 11-11-2014, 07:58 AM
  5. access 2010 windows7 nas linked tables
    By mikeb in forum Access
    Replies: 7
    Last Post: 03-18-2011, 03:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums