Version 3.16 Approx 0.4 MB First Published 30 Sept 2020 Last Updated 26 July 2023
Background Info:
For many years, Access databases have been able to create up to 256 simultaneous connections to local and linked tables.
This limit was increased in a recent update. See Update 2 and Update 3 below.
When a large number of connections are left open, the performance of the application will deteriorate & eventually it may crash with a message:
'Cannot open any more tables' or 'Cannot open any more databases'.
The attached utility can be used to monitor the number of open databases/tables that can be created before these errors appear.
It was originally written by Ben Sacharich in 2008 and has been updated by myself with additional functionality
This info can help developers reduce the number of connections used by database objects.
Downloads
Click to download:
ACCDB version:
Available Connections v3.16 ACCDB file (zipped)
MDB version:
Available Connections 2003 v3.16 MDB file (2003 file format) (zipped)
Typical Usage:
The utility contains one form and one report. Import both items into your database.
When you want to run a check, load the form frmAvailableConnections. Ideally, do so just after starting your database.
Depending on the version of Access used, you should have up t0 256 (or 512) connections available unless you already have something in use e.g. a startup form
NOTE: The form used by the utility is, of course, also using several connections whilst monitoring your applications.
Access 365 - Version 2304 or earlier
Access 365 - Version 2305 or later
Leave the form open but move it to an out of the way position on your screen
Run other tasks and click Requery on the form or set to Auto Requery at specified time intervals using the combo box.
The number of available connections will drop each time something new is opened.
Click the Print button to open the report at any time or to save a hard copy for future use.
The number should recover as items are closed . . . providing everything really is being closed and recordsets set to nothing after use.
One possible scenario for using this is to monitor issues where you are unable to run a backup or restore file due to error 70 (permission denied).
This can occur if you do not have permission to edit files in the affected folder.
It can also happen when there is a persistent connection running in the background
If neither of those are an issue, prepare to run your backup/restore.
Check your available connections. Is anything still running?
If necessary, close all open objects listed then requery the form.
Have the connections recovered?
Does it list anything still open?
NOTE:
Each reference to a local table, linked SQL table or query object uses 4 connections.
A reference to a linked Access table, spreadsheet or text file uses 6 connections.
Review the queries you are calling from form, combo box, and listbox objects.
Remove extraneous references to tables and sub-queries to reduce overhead.
With thanks to Ben Sacharich and AWF member moke123
UPDATE 1: 16 Oct 2022
This application is also now available as an Access add-in which can be run directly from any Access app
The add-in version is available from
Available Connections Add-In
UPDATE 2: 28 Jun 2023
Internal limits on the number of database connections and open tables/queries have been increased in a recent update (version 2305).
The screenshot below indicates that the number of available connections has been doubled to 512.
Similarly, the maximum number of open tables including internal tables used by Access seems to have doubled from 2048, as currently listed in
Access specifications
The new limit for the number of open tables is 4096.
Taken together, these changes should help to improve overall performance.
The changes should also significantly reduce the number of ‘cannot open any more databases’ and 'cannot open any more tables' messages.
Many thanks to the Access team for making these important background changes to the Access architecture
UPDATE 3: Version 3.16 - 26 July 2023
This version fixes an issue in 64-bit Access where the new limits caused the available connections count to trigger error 28 - out of stack space
The original fOpenDatabaseCount function has been replaced by CountAvailableConnections which works correctly in both 32-bit and 64-bit Access.
Many thanks to Shane Groff from the Access team for suggesting this new approach.
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 26 July 2023
|
Return to Example Databases Page
|
Return to Top
|