Version 3.16           First Published 16 Oct 2022                 Last Updated 16 Aug 2024                 Approx 0.7 MB (zipped)


Section Links (this page):
          Background Info
          Download
          Typical Usage
          Connections info
          Update 1
          Update 2
          Update 3
          Installation Instructions
          Acknowledgements
          Feedback


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

THREE different versions are now available for A2010 (or later), A2007 or A2000-A2003


Background Info                                                                                                                      Return To Top

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                                                                                                                                  Return To Top

Click to download the appropriate version of the add-in for your version of Access:

      A2010+ ACCDA add-in & PDF file:        Available Connections Add-In version 3.16 (Access 2010 or later)     0.7 MB     (zipped)

      A2007 ACCDA add-in & PDF file:          Available Connections Add-In version 3.16 (Access 2007)     0.7 MB     (zipped)

      A2000 MDA add-in & PDF file:              Available Connections Add-In version 3.16 (Access 2000)     0.6 MB     (zipped)

Download and unblock the zip file. For more details, see my article: Unblock downloaded files by removing the Mark of the Web

Unzip and save the files to the same folder in a trusted location.

To use the utility as an Access add-in, follow the Installation Instructions below .


Typical Usage                                                                                                                           Return To Top

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                                                                                                                     Return To Top

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                                                                                  Return To Top

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                                                                        Return To Top

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                                                                                 Return To Top

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                                                                                                          Return To Top

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


Acknowledgements                                                                                                                  Return To Top

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                                                                                                                                    Return To Top

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 16 Aug 2024

Return to Access Add-Ins Page

Return to Top