Bug Alert Follow-Up: CurrentDb and DAO.OpenDatabase

^{Point of clarification for my fellow pedants:  despite the text in the article's cover image above, this article is less of a bug "fix" and more of an explanation of apparently buggy behavior.  Unfortunately for us pedants, pith trumps clarity in cover image design.}

I documented what I assumed was a bug last week in my article, BUG ALERT: CurrentDb and DAO.OpenDatabase.

It turns out that if you callDAO.OpenDatabasebefore your first call to CurrentDb, then when you do call CurrentDb, you lose exclusive access to your front-end database.

I provided the Microsoft Access development team a link to the article via the Access MVP mailing list and asked them to investigate the issue.

Shane Groff, a longtime member of the Microsoft Access engineering team, wrote back with a full explanation for the observed behavior (shared with permission):

Ok, I have an explanation for the behavior, and it is by design, although I agree that it is not obvious what is happening.

First, it is never the case that “you lose exclusive access to your front-end database” as you state on your page about this issue.

However, you may be prevented from getting exclusive access in the UI if you run code and don’t already have exclusive access.

When you initially create a new database, you have exclusive access, but when you open an existing database, you do not have exclusive access, unless you do something (like open an object in design view) that requires exclusive access.

The key to understanding what is happening here is the concept of DAO Workspaces (Workspace object (DAO) | Microsoft Learn).  A database opened or referenced in code is associated with a particular Workspace.  If a database is opened in one Workspace, it can’t be opened for exclusive access in a different Workspace (or in the Access UI).

From this article, “When you first refer to or use a Workspace object, you automatically create the default workspace.”

If you follow the steps exactly as you describe in your mcve (create a new database, add the code, then run it).  You will not get the message “Microsoft Access can’t save design changes” when trying to edit an object, instead you will get an error immediately when trying to reference CurrentDb, “Run-time error ‘3045’: Could not use <database name>; file already in use.”

This happens because when you opened the temp database, that caused a new Workspace object to be created as the default Workspace.  At this point the default Workspace contains only the temp database.  It does NOT contain the user database.  So when you reference CurrentDb (the user database), it doesn’t find it in the Workspace, so it tries to open it in the default Workspace.  Since it is already opened for exclusive access, this fails, and you get the message that the file is already in use.

With a slight variation from your mcve, which is what I assume you actually did:

1. Create a new database
2. Put your code in a new module
3. Close the database
4. Reopen the database
5. Execute the code

Then, you will get the message when trying to design an object that “You do not have exclusive access to the database”, and if you try to save an object, that “Microsoft Access can’t save design changes…”

In this case when you open the database, you do not have exclusive access.  When you run the code, again, it creates a default Workspace when you create the temp database, and it only contains the temp database.  Then when you reference CurrentDb, it doesn’t find the user database in the default Workspace, so it opens it in the default Workspace.  It is now open twice, once in Access, and once in the default DAO Workspace.

Now, if you try to edit an object in the Access UI, it tries to get an exclusive lock, but that fails because it is open in the default Workspace.

So this is only an issue when you create a default Workspace that doesn’t include the user database (calling CurrentDb first creates the default Workspace with the open user database in it), and then reference the open user database (e.g. by calling CurrentDb), and then try to get exclusive access to the user database (e.g. by editing an object).

It might make sense for us to always add the user database to the default Workspace, regardless of when it is created, but I don’t think we’d change this kind of behavior because it would be difficult to be certain that it wouldn’t have unintended consequences.

Another way to workaround the behavior is to get exclusive access to the database (by opening an object in design view) before executing any code.  This may still cause an error in the code if it tries to re-open the database in the default Workspace if the default Workspace doesn’t contain the user database, but it will prevent you from being unable to get exclusive access.

Very special thanks to Shane Groff for providing his expertise in helping solve this puzzle.

And, for what it's worth, I tend to share Shane's reluctance at the idea of changing this behavior "because it would be difficult to be certain that it wouldn't have unintended consequences." No doubt.

Referenced articles