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.
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.
Ben Sacherich, Pittsburgh, PA
Labels: Access, Jet