News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, April 16, 2014

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

Labels: , ,

Tuesday, April 01, 2014

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

Labels: ,

Thursday, March 27, 2014

The Microsoft Access Support Team Blog

One more Access blog, now from Access support team - The Microsoft Access Support Team Blog. Check it out!


Thursday, March 20, 2014

Navigation Pane in Access 2010

Get lost with Access Navigation pane? Here some great tips from Albert D. Kallal how to be even more productive with the navigation pane in Access 2010: Become friends with the nav pane in 2010

Also look at Microsoft Access Shortcut Keys for Data Entry and Navigation from FMS.


Thursday, March 13, 2014

SharePoint Apps with Microsoft Access

Here three session recording from latest SharePoint Conference on building Access apps on SharePoint:


SPC204: Anyone can build a SharePoint App with Microsoft Access


SPC338: The 'how to' guide for selling and managing SharePoint Apps built using Access


SPC335: Rich extensions to SharePoint Apps using Microsoft Access


Wednesday, February 26, 2014

Office 2013 Service Pack 1

Microsoft has announced availability of Service Pack 1 (SP1) for the Office 2013 and SharePoint 2013. According to Issues Fixed by Service Pack 1 several Access crashes were fixed, the only new feature introduced with SP1 is "Access app developers can now upgrade the apps they have made available in the Store or a corporate catalog".


Thursday, February 20, 2014 new address

Just to keep it here: as is no longer exists, there is a "saved copy" of it at