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..."

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:

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.

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.

Further reading

CurrentDb puts me in locked mode.

Hello, The second function called by my main sub contains the following line: Set rs = CurrentDb.OpenRecordset(“Parametres”) If I have changed a piece of code before running it, it raises an error: “The database has been placed in a state by user ‘Admin’ on machine ‘numbers’ that prevents...

Access World Forumssmbrr