An Access database can create up to 255 simltaneous connections to local & linked tables.
However, if 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.
The utility contains one form and one report. Import both items into your database.
When you want to run a check, run the form frmAvailableConnections.
Ideally, do so just after starting your database.
You should have 255 connections unless you already have something in use e.g. a startup form
Leave the form open but put it in an out of the way position on your screen
Run other tasks and click requery on the form.
The number of connections will drop each time something new is opened
They should recover as items are closed ...providing everything is being closed and recordsets set to nothing after use.
One possible scenario for using this is 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 that's OK, 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?
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 listview objects.
Remove extraneous references to tables and sub-queries to reduce overhead.
Updated Colin Riddington 2018.
With thanks to Ben Sacharich and AWF member moke123
Click to download: Available Connections (zipped)
Click any image to view a larger version ...