A reader emailed me recently to ask for help with strange behavior in one of his programs.
It turns out that if you call
DAO.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 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:
Adminwith a capital "A"
adminwith 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...
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
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.