Two important changes were made to the Access architecture in version 2305 which was released to the Current Channel early in 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 error messages similar to those above . . . often followed by the application freezing / crashing.
These errors are due to two limits built into applications created in all Access versions and in both 32-bit and 64-bit Access.
1. The maximum number of open tables
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 more details, see this article on my website.
When this number grows too large, performance will deteriorate before Access eventually crashes with error 3014: cannot open any more tables.
However, there is no realistic way for a developer to keep track of the actual number of open tables at any time.
2. The maximum number of available connections
For many years, Access databases have been able to create up to 256 simultaneous connections to local and linked tables. This limit was doubled to 512 in version 2305.
The number of connections can be monitored using my Available Connections add-in.
Typically, each reference to a local table or linked SQL table uses 4 connections but each 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 and the row sources of each combo box or list box contained in those objects.
When a large number of connections are left open, performance will deteriorate and eventually it may crash with error 3048: cannot open any more databases.
Doubling the maximum number in each case should significantly reduce the likelihood of ever hitting the limits.
Even so, it remains good practice to only open objects when actually needed and close them again after use.
NOTE:
The changes only apply to current Access versions.
Older Access versions prior to 2016 are not affected.
Comments