BUG ALERT: CurrentDb and DAO.OpenDatabase

A reader emailed me recently to ask for help with strange behavior in one of his programs.

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

"You do not have exclusive access..."

Error message, "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later."

The above error message appears most commonly (and correctly) in the following three scenarios:

Two different users are making design changes at the same time to a front-end file on a network folder
One user opens a front-end file then a second user logs in with a different profile (without the first user logging off) and opens a second copy of the front-end file
One user launches a second instance of an already-open front-end file

In this situation, though, I believe the message is the symptom of a bug in Microsoft Access.

"Microsoft Access can't save design changes..."

If you ignore the earlier message, make changes to your database, and then try to save them, you will get the following message:

Error message, "Microsoft Access can't save design changes or save to a new database because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file."

The MCVE

The Holy Grail of debugging is to be able to produce a "minimal, complete, and verifiable example" of the bug in question.

To reproduce this bug, create a new Access database file and add the following code to a standard code module:

Sub DemonstrateCurrentDbBug()
    'Debug.Print CurrentDb.Name  'Uncommenting this line avoids the bug
    
    'Create an external database file
    Dim fpTemp As String
    fpTemp = CurrentProject.Path & "\" & Format(VBA.Now, "yyyymmdd-hhnnss") & ".accdb"
    DAO.CreateDatabase fpTemp, dbLangGeneral
    
    'Open the database
    Dim TempDb As DAO.Database
    Set TempDb = DAO.OpenDatabase(fpTemp)    '<-- Calling this line...
    
    'Call the CurrentDb function
    Debug.Print CurrentDb.Name      '<-- ...before this line creates the bug
    
    'Congratulations, you no longer have exclusive access to your front-end
    
    'Clean up the temp file
    TempDb.Close
    Set TempDb = Nothing
    Kill fpTemp
End Sub

A Tale of Two Admins

Right after executing the Debug.Print CurrentDb.Name line, the front-end database file's lock file (*.laccdb) has two "Admin" entries:

Admin with a capital "A"
admin with a lower-case "a"

I'm not sure what, if any, significance there is to that observation, but it seems noteworthy.

"Admin! admin!" I should change my computer name from "MJW20" to "Pizza" and see if I could get Little Caesar's to sponsor this article. 🤔

A Reliable Workaround

The hardest part of troubleshooting this issue was isolating it.

The fix itself is quite straightforward: call CurrentDb first, before any calls to the DAO.OpenDatabase method. To implement that fix in our above code sample, simply uncomment the first line:

Sub DemonstrateCurrentDbBug()
    Debug.Print CurrentDb.Name  'Uncommenting this line avoids the bug
    '... other code...

My Advice

While I'm going to stop short of recommending that you put a call to CurrentDb as the first line in every applications' StartUp routine, it's definitely something I would try if I got either of the above error messages with no alternate explanation for them.

In the wild, it's very unlikely that the calls to CurrentDb and OpenDatabase will be right next to each other as in my example function above.

Instead, the calls will likely be completely separate and the order in which they end up being called may vary from one execution of the program to the next. This could result in the "exclusive access" symptom appearing intermittently...and those types of bugs are nearly impossible to troubleshoot.