Partial Workaround for the "Could not lock file" bug

[NoLongerSet]

If you use the DBEngine.OpenDatabase method and started getting the following error message in your Access applications, you are likely a victim of a recent Microsoft security fix that had some nasty side effects for us Access developers.

I wrote a function that acts as a partial workaround for this issue. Namely, it helps re-enable single-user access to backend databases when using the OpenDatabase method, but it still does not solve the (bigger) multi-user issue. However, it can still be valuable in some scenarios (such as during development).

The full article is here: Partial Workaround for the "Could not lock file" Bug

Here's the raw code:

'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
'
'Source: https://nolongerset.com/could-not-lock-file-workaround/
'
'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

 

[shadow9449]

Hey @NoLongerSet (Mike?)

I just want to let you know that I found out about your blog a few weeks ago from Karl's newscast and I immediately bookmarked it and read it every day.

To anyone looking for an awesome Access blog, you want to check out https://nolongerset.com/ !!!

Kudos

 

[NoLongerSet]

Thanks, @shadow9449! And, yes, it is Mike (Wolfe).

 

[arnelgp]

However, it can still be valuable in some scenarios (such as during development).

why would you connect to Live data when you are in development stage?
and how come a development db shared in production?

does a local copy (not shared) can be locked too?!

 

[NoLongerSet]

The issue that this fixes is one where it's not possible to open an .mdb / .accdb file using DBEngine.OpenDatabase, even when you are the only one trying to access the file. The partial workaround does not allow the backend file to be opened by multiple users simultaneously. That's why I stated it would be most useful in development (i.e., when only a single person will be accessing the file). The issue apparently crops up even on a development machine accessing a local development copy of an .mdb/.accdb.

That said, I was not able to reproduce the original error myself, so I can't say for sure whether the function is useful at all. But, I thought it was important to broadcast Shane's insights to as big an audience as possible.

 

[AdamGaffney96]

Thanks a lot @NoLongerSet.

This issue hit us like a train as several of our systems became very difficult to use right over Christmas time. As the main developer I felt like I was going a little crazy trying to track the source! I managed to identify that it likely was due to the security update as people that hadn't updated yet weren't getting the issue, unfortunately the update has now been forced and so all the multi-user DB's are broken. I understand your workaround won't help so much in my situation as it doesn't fix the multi-user issue, but just seeing someone else have the issue and find the reason is a weight off my shoulders.

Could I ask, you mentioned that you got this info from an Access Developer (Shane) newsletter of sorts. Would it be possible for you to link me to where I can sign up for this newsletter? This seems like it'd be incredibly important for me to keep an eye on as I'm currently making a whole new (temporary) system to replace our now broken ones, but it'd be good to know if we could ever switch back as a fix is made.

 

[GPGeorge]

Shane Groff is a Microsoft employee, in fact, he's the longest serving member of the Access development team. He has no newsletter as such. However, he has been very proactive in engaging with the Access community and the Access MVPs in providing insight and feedback on issues like this. Shane is a most valuable person and deserving of our highest respect and appreciation.

 

[AdamGaffney96]

Shane Groff is a Microsoft employee, in fact, he's the longest serving member of the Access development team. He has no newsletter as such. However, he has been very proactive in engaging with the Access community and the Access MVPs in providing insight and feedback on issues like this. Shane is a most valuable person and deserving of our highest respect and appreciation.

Ah interesting, thanks for the info. He seems very helpful and this is the only other place I've heard someone talk about this issue that hit us about 3 weeks to a month ago now. Hopefully we can be kept up to date somehow if this is ever resolved.

 

[GPGeorge]

Ah interesting, thanks for the info. He seems very helpful and this is the only other place I've heard someone talk about this issue that hit us about 3 weeks to a month ago now. Hopefully we can be kept up to date somehow if this is ever resolved.

Keep checking Mike's blog and also a blog maintained by Daniel Pineault, who tracks the bug closely. There are, no doubt, others, but for details and continuity they are the most reliable at the moment.

 

[AdamGaffney96]

Thanks for that link, after doing some digging around the fix mentioned in that I think our network drives are compatible with the fix. I know it's been difficult for some, but I'm going to press our IT department to push that update and then try and update if the fix works. Unfortunately just a lot of layers of bureaucracy between the update being pushed and our machines getting it.

 

[NauticalGent]

Thanks for that link, after doing some digging around the fix mentioned in that I think our network drives are compatible with the fix. I know it's been difficult for some, but I'm going to press our IT department to push that update and then try and update if the fix works. Unfortunately just a lot of layers of bureaucracy between the update being pushed and our machines getting it.

Please report back here on how you fare - either way - some real world examples are a real value to all.

 

[AdamGaffney96]

Unfortunately had no luck with our IT pushing the update as they "no longer support" access, so likely won't be able to test out the new update. We've been able to go with a new Excel solution instead thankfully, but apologies I can't give much more news.

 

[Minty]

It's an office update - that happens to fix an Access bug. Tell them it has also "affected" Excel and I bet they will get it out quickly...