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.
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 ...