Alex & Access

April 1, 2014

How many database connections are still available in an Access database

Filed under: Access — access @ 1:58 pm

Finally I got a reply from Ben Sacherich, who mentioned, that he "have a pop-up form that tells you how many database connections are still available and how many have been used", responding to a post  One more source for Can’t open any more databases.
Thanks Ben!

I created a form that will tell you how many “database connections” are still available in an Access database.  This is useful for debugging the dreaded ‘Cannot open any more tables‘ and ‘Cannot open any more databases‘ errors.


Use this dialog to monitor the number of open databases/tables that can be created before the error will show up.

Note that every reference to a local table or query object uses 1 connection.   A reference to a linked table uses 2 connections.  Review the queries you are calling from form, combo box, and listview objects and remove extraneous references to tables and sub-queries to reduce overhead.

I added this form to an Access Add-in I had already developed so it’s available to any database. You can do the same, or just add this one form to your existing database.  It opens as a dialog so you can position it on another monitor.


How does it work?   Every time you press the Requery button, it runs a recursive function that opens one database connection, increments a counter, and then calls itself.   When it finally gets an error because it has opened all of the connections, it closes all the connections it created and returns the maximum number from the counter. 

I feel that my solution is a big help for this error as it’s not very understood.   I read a lot about this online and some people think Access/Jet has around 2,000 TableID’s but that number doesn’t match up with what my form reports.  The numbers reported by my form align perfectly with the error.   It may be counting something different than TableID’s but it provides an accurate gauge to measure the amount of connections being used as you open new objects.

Download solution

Ben Sacherich, Pittsburgh, PA


  1. Just wanted to thank you, i searched Days to find a solution and yours is the only one to gauge is limit of TableID’s. THANK YOU THANK YOU THANK YOU!!!

    Comment by mrpie95 — June 25, 2019 @ 6:12 pm

  2. Someone made some additions to my version and it is hosted here:

    Comment by bsacheri — July 20, 2019 @ 8:52 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: