Partial Workaround for the "Could not lock file" Bug

A recent Windows security fix broke the Microsoft Access database locking mechanism.

One of the error messages associated with that bug is AccessError(3050), "Could not lock file."

This error occurs when you try to run the DAO.DBEngine method OpenDatabase, but ACE (the database engine) is unable to create a lock file (.ldb).

The Role of the `.ldb` Lock File

When you open a file using the Windows API (which is what every Windows application actually does behind the scenes), there are several different modes that you can use.

You can open a file for exclusive use, meaning no other process can read, write, or delete that file (for example, when you open an Access database and pass the /excl flag on the command line). You can open a file and allow other processes to do all of those things–read, write, and even delete the file. This is the default mode when opening a file with Notepad. You can open a file and allow other processes to read the same file, but not edit or delete it. This is the default file locking mode in Excel, for instance.

However, there is no combination of these modes that provides the specific file access mode that the ACE database engine prefers.

And what combination is that? By default, the ACE database engine will allow multiple people to edit the contents of the tables at the same time. However, if you want to edit the structure of the tables (or the design of the forms or reports or edit VBA) then you need to be the only user currently accessing the database file. So how does Access deal with this scenario if no combination of the file sharing and access modes will support it?

Enter the Access lock file.

How the Lock File Works

When you open an .accdb or .mdb file or view a table or query linked to such a file, the ACE database engine checks to see if there is an existing .ldb file.

If there is no such file, then the engine creates one. If, for whatever reason, it is unable to create a lock file, the engine will fall back on trying to open the Access file in exclusive mode (dwShareMode set to zero). If this succeeds, it means that no one else can make changes to the file while you have it open–to include editing the contents of the tables...but at least you were able to open it.

When you use the OpenDatabase method of the DBEngine object, that same behavior is not built-in.

The Bug-Inducing Behavior of the Recent Security Fix

Reading between the lines of the various error reports that I've seen over the past several days, it appears that the root of the current bug is the ACE database engine's .ldb file-locking mechanism.

It seems that the recent security fix has done something to prevent the database engine from creating the .ldb file in certain situations. It's not yet clear to me what those situations are and what the nature of the security vulnerability is. I have this nagging fear that the security team and the Access team at Microsoft are at loggerheads on this one.

I'm afraid that the security team is saying, "We're sorry, you simply can't continue creating this .ldb file in whatever way it is that you are creating it when conditions A, B, and C are met."

To which the Access team is responding, "Do you have any idea how many business critical applications this breaks? You're asking us to redesign one of the foundational aspects of how the ACE database engine works. That's not something you simply shove into a hotfix."

Right now, this feels eerily similar to the monster bug from almost three years ago that remains unfixed. Let's hope this isn't monster bug 2.0.

The Partial Workaround

Shane Groff from the Access development team offered a partial workaround for this bug as it relates to the DBEngine.OpenDatabase method (shared from the Microsoft NDA mailing list with Shane's permission):

The reason that you are getting an error using DBEngine.OpenDatabase is that Access has logic that says if it can’t open the locking file, to revert to just opening the file for exclusive use (that’s why people are getting an error when a second user tries to open the database).

That logic is not part of the OpenDatabase command, so the work around is to implement the logic, and if it fails with error 3050, then set the Option parameter (DBEngine.OpenDatabase(“t:\foo.accdb”, TRUE)) in order to explicitly request an exclusive lock, in which case Ace will not attempt to use the locking file, and you won’t get an error.

A Temporary Replacement Function for DbEngine.OpenDatabase

Based on Shane's email, I created a function that will replicate the behavior of msaccess.exe from within VBA. Unfortunately, I could not replicate the error on my own system, so I have no way to verify for sure that this function actually–you know–works. But, based on Shane's explanation, it seems that it should (subject to the limitations with regard to single-user access to the file).

The sample code is below. If you are getting this error and the code below works for you, please let me know in the comments below. Thanks!

'This is a temporary workaround for the December 2021 Access bug:
'   https://nolongerset.com/bug-alert-file-in-use-could-not-lock-file/
'Note that this fix works only for *single-user* access to the Access file,
'   as it falls back on opening the file for exclusive use
'
'USAGE:
'   REPLACE:  Set MyDb = DBEngine.OpenDatabase(PathToMyDb)
'      WITH:  Set MyDb = OpenDatabase(DBEngine, PathToMyDb)
Function OpenDatabase(Engine As DAO.DBEngine, PathToDB As String, _
                      Optional Options As Variant = False, _
                      Optional ReadOnly As Variant = False, _
                      Optional Connect As Variant) As DAO.Database
    On Error Resume Next
    Set OpenDatabase = Engine.OpenDatabase(PathToDB, Options, ReadOnly, Connect)
    
    Select Case Err.Number
    Case 0  'no error; safe to return
        Exit Function
    Case 3050  'Could not lock file.
        'Attempt to open file with exclusive access
        Err.Clear
        Set OpenDatabase = Engine.OpenDatabase(PathToDB, True, ReadOnly, Connect)
        If Err.Number = 0 Then
            'If it succeeds, notify the developer then exit the function
            Debug.Print PathToDB; " opened with exclusive access only"
            Exit Function
        End If
    End Select
    
    'If we got here it means that the workaround did not fix the problem;
    '   (maybe another user has the file open and we can't get exclusive access)
    '   we'll nullify the On Error Resume Next handler and re-run the method
    '   so that we properly propagate the original error up the call stack
    On Error GoTo 0
    Set OpenDatabase = Engine.OpenDatabase(PathToDB, Options, ReadOnly, Connect)
    
End Function

Epilogue

Since I theorized about a possible internal struggle at Microsoft in the same article that I quoted a member of the Access team from the NDA (signed non-disclosure agreement) email list, I wanted to make it absolutely clear that my theory is not based in any way on anything I've read on that mailing list.

I'm merely speculating that Microsoft's security team has an entirely different set of priorities than one of their development teams. If you've ever worked at any organization with a dedicated security team, you'll know that this is not some big leap of logic. Rather, it's simply a typical manifestation of the tradeoffs between security and convenience in almost every area of our lives.

Shane does a fantastic job providing updates to the Access MVPs. I've never seen him write a single disparaging word about Microsoft or any of his colleagues. If I were to get Shane in trouble, I'd have hell to pay from the other Access MVPs.

By the way, Shane, thanks again for letting me publish the excerpts from your email!