Access 2013 SP1 Runtime

The Microsoft Access 2013 Runtime enables you to distribute Access 2013 applications to users who do not have the full version of Access 2013 installed on their computers. Now version 2013 SP1 is available for download

How to detect, if you are running 64-bit Office

New data type LongPtr is Long integer on 32-bit systems and LongLong integer on 64-bit systems, by checking it VarType you can find out on what environments you run your VBA code:

Function Is64BitOffice() As Boolean
    Dim lp As LongPtr
    Is64BitOffice = Not (VarType(lp) = vbLong)
End Function

This trick I learned from fellow MVP Brent Spaulding

How many database connections are still available in an Access database

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