First Published 4 July 2023                       Last Updated 2 Nov 2023

Two important changes were made to the Access architecture in version 2305 which was released to the Current Channel in early June 2023.
1.   The maximum number of open tables - doubled from 2048 to 4096
2.   The maximum number of available connections - doubled from 256 to 512

These changes certainly do not sound very exciting but they do have important implications.
However the changes were made without any public announcement. Many developers will still be unaware of what has changed and how it will affect their applications.

For many years, Access users will have experienced occasional messages similar to those below . . . often followed by their application freezing / crashing.

Unfortunately these messages are not at all helpful in assisting developers make the changes needed to avoid problems in future.

These errors are due to two limits built into applications created in all Access versions and in both 32-bit and 64-bit Access.

I will try and explain based on discussions I've had with other long-time Access developers, together with additional information from a member of the Access team.
The two issues are inter-related to some extent



1.   The maximum number of open tables

      The Access specifications article has ben recently updated and now shows both the old and new limits for this item


      Q:   What does the "maximum number of open tables" of 2048/4096 actually mean?

      A:   This refers both to tables (and queries) that are opened directly by the user / application and to numerous table IDs opened indirectly by Access in the background.

            For example:
            •   Queries including SQL statements in VBA
            •   Form / report record sources
            •   Combobox / listbox row sources
            •   Recordsets
            •   Domain functions (DLookup / DCount etc)
            •   Embedded images referenced in the MSysResources system tables etc . . .
            •   Deep hidden attached tables that are opened in the background when any complex datatypes are used in tables
            •   Temporary tables created as part of query execution plans
            •   Table IDs used by Access to fetch data in a form, check the position of the current record, fetch bookmarks etc
            •   Table IDs used by Access to reference table columns (fields). It does this by asking the ACE database engine which in turn uses another table ID etc ...

      As you can see, this gets very complex and there is no realistic way for a developer to keep track of the actual number of open tables at any time.

      When this number grows too large, performance will deteriorate before Access eventually crashes with error 3014: cannot open any more tables

      Doubling the maximum number from 2048 to 4096 should significantly reduce the likelihood of ever hitting the limit

      The golden rule of not opening an object until it is actually needed and closing it afterwards will also help make it less likely that any Access database will reach the limit.

      For example:
      a)   avoid the use of subdatasheets - a known 'performance killer'
      b)   recordsets should always be closed and set to nothing after use.
      c)   tabbed forms with several subforms on multiple tabs should involve 'lazy loading' where the subform record source is only set when the tab page is opened . . .
           . . . and cleared when moving to another tab page



2.   The maximum number of available connections

      Q:   What does the "maximum number of available connections" of 256/512 actually mean?

      A:   For many years, Access databases have been able to create up to 256 simultaneous connections to local and linked tables.
            This limit has also been doubled to 512 in version 2305

      When a large number of connections are left open, the performance of the application will deteriorate.
      Eventually it may crash with error 3048: cannot open any more databases

      Unlike the number of 'open tables' currently in use, the number of connections can be monitored using the Available Connections utility available on this website.

      Based on an original idea by Ben Sacharich, the utility is now available both as an Access add-in or as a stand-alone utility for integrating into your own apps



      The following information is a guide to the typical number of connections used by different Access objects and controls
      Each reference to a local table or linked SQL table uses 4 connections.
      Each reference to a linked Access table, spreadsheet or text file uses 6 connections.

      Queries use 2 or more connections depending on the query type
      This applies to the saved or temporary queries used as record sources in bound forms and reports
      It also applies to the row sources of each combo box or listbox contained in those objects

      Unbound forms and reports use no connections. Hence the reason for 'lazy loading' mentioned earlier



NOTE:
1.   The Access team intends to monitor the effect of recent changes on the number of 'cannot open any more databases' and 'cannot open any more tables' messages that are
      reported due to applications crashing. If necessary, further changes may be made to help fix such issues

2.   The changes only affect Office 365 and C2R consumer versions of Access 2016, 2019, and 2021. They do not affect 2016 MSI or 2019/2021 Volume License versions.
      Older Access versions prior to 2016 are also not affected



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 2 Nov 2023

Return to Access Blog Page

Return to Top