Version 3.16 First Published 16 Oct 2022 Last Updated 26 Jul 2023 Approx 0.7 MB (zipped)
This is an updated version of my Available Connections utility app.
This new version is available as an Access add-in so it can be run directly from any Access app
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 and required a code change for 64-bit Access. See Update 2 and Update 3 below.
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 add-in can be used to monitor the number of open databases/tables that can be created before these errors appear.
It is based on an old utility originally written by Ben Sacharich in 2008 and has been updated by myself with additional functionality
The info provided can help developers reduce the number of connections used by database objects.
Download
Click to download:
ACCDA add-in & PDF file:
Available Connections Add-In version 3.16 0.7 MB (zipped)
Typical Usage:
The utility contains one form and one report. Both of these are available when the app is loaded as an add-in (see below).
When you want to run a check, run the add-in to load the Available Connections form. Ideally, do so just after starting the database being monitored.
Depending on the version of Access used, you should have up to 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 Available Connections form open but move it to an out of the way position on your screen
Run other tasks / open new forms etc. The connections info is updated automatically every 10 seconds.
The number of available connections will drop each time a new object (table/query/form/report) is opened.
However, the number of available connections should recover when the objects are closed.
For that reason, it is normally good practice to close open objects and reset recordsets to nothing when no longer in use
NOTE:
a) You can alter the refresh frequency by changing the specified time intervals using the combo box.
b) Alternatively, set Auto Requery to Off using the combo box and click the Requery button when it suits your needs.
c) Changing the refresh frequency will clear the previous log history and start a new log
d) Click the Print button to view the log as a report at any time or to save a hard copy for future use.
As stated above, the number should recover as items are closed . . . providing everything really is being closed and recordsets set to nothing after use.
Connections Info:
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
This utility can assist reviewing the effect of queries called from form, report, combo box and listbox objects.
Wherever possible, remove extraneous references to tables and sub-queries to reduce overhead.
Also be aware that if you use subdatasheets (not recommended), each expanded subdatasheet will use additional connections.
For example, a table with 5 open subdatasheets used a total of 24 connections
Modules and macros do not use any connections so are excluded from the list by default.
UPDATE 1: Version 3.14 - 20 Oct 2022
The app has been updated to version 3.14 following some very helpful feedback from ex-MVP, Jack Stockton
The latest version fixes a number of bugs and adds additional functionality including:
a) NEW: Added Pause Log button to temporarily pause code without losing previous history
b) FIXED: Open tables & queries were not disaplyed - these are now listed in form window
c) FIXED: Issue causing initial connections count to differ from subsequent counts
d) FIXED: Error where integer character limit can be exceeded when logging is left to run for a long period of time
When this happens, the previous log history is cleared and logging continues
One possible scenario for using this add-in 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?
UPDATE 2: New Access limits - 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.
NOTE:
A standalone function CountAvailableConnections is also provided in modFunctions. This can be used independently without the main form being open
Many thanks to Shane Groff from the Access team for suggesting this new approach.
Installation Instructions:
1. Download the zip file to your computer
The zip file contains the add-in (ACCDA file) and a PDF file with installation instructions.
Unzip and copy the file, AvailableConnectionsAddIn_v3.16.accda to your Access add-ins folder.
The folder path is usually: C:\Users\YourWindowsUserNameGoesHere\AppData\Roaming\Microsoft\AddIns
NOTE:
You will need to tick Show Hidden Items in File Explorer to see the AppData folder
2. Browse to and run the .accda application from your AddIns folder to 'register its location'. This message appears:
The path settings have been updated in a hidden system table USysRegInfo. Click OK to close the add-in
3. Open any Access app and go to Database Tools . . . Add-Ins on the ribbon
4. Click Add-in Manager. This dialog appears showing all the available add-ins on your workstation. Installed add-ins are marked with a X
Select Available Connections Add-In from the list and click Install then click Close.
The Available Connections add-in is now available for use with all your Access apps
5. You can also install Add-Ins from the File . . . Options . . . Add-Ins menu
Select Access Add-ins on the dropdown then click Go
The Access Add-in Manager will open - same as step 4
6. To run the add-in, again go to Database Tools . . . Add-Ins on the ribbon and select Available Connections from the list
The Available Connections form will open in your app and start monitoring the number of connections in use.
7. The form will appear close to the top left corner of the screen.
If necessary, it can be moved to any more convenient location on the screen
8. If you close the Available Connections form at any time, just repeat step 6 to re-open it
With thanks to:
Ben Sacharich for the original idea used in this application
Dale Fye for code used in converting this utility to an add-in
Jack Stockton for feedback on bugs and suggesting various improvements
Shane Groff for suggesting new code to fix the out of stack space error
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 Access Add-Ins Page
Return to Top