MaxLocksPerFile / File sharing lock count exceeded

[Thales750]

This is a brand new problem. Fist time ever to see it since getting started with Access 97
I just wanted to make sure it's not another update induced failure.

Microsoft says to do this:
https://www.anysitesolutions.com/access-maxlocksperfile-file-sharing-lock-count-exceeded/

Edit: That was total BS it ended at Office 2010


Thanks.

 

[Thales750]

These guys:
https://docs.microsoft.com/en-us/office/troubleshoot/access/file-sharing-lock-count-exceeded

Say to look here:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE
It does not exist, or I can't seem to locate it.

 

[isladogs]

I have written several detailed posts on this topic over the past couple of years.
Unfortunately, the site Search & Similar Threads features both seem to be down at the moment.
However when normal service is resumed, search for MaxLocksPerFile and Isladogs to get a list of relevant links

The default registry value for MaxLocksPerFile = 9500 but it can be increased ...in small steps
In the meantime, the registry key location(s) will depend on your Access version & bitness as well as your Windows bitness
It is possible no key may exist if you've never specified a value - or you may have multiple such keys
Do a registry search for MaxLocksPerFile

In addition, registry keys for MaxBufferSize may be relevant.

Other relevant items include:
a) the number of available connections - see Available Connections - Mendip Data Systems
b) large address awareness - see Phillip Stiefel's webpage mentioned in this post: Use large address awareness to fix out of memory issues in 32-bit Access - Microsoft Access / VBA (bytes.com)

Hope that helps

 

[theDBguy]

These guys:
https://docs.microsoft.com/en-us/office/troubleshoot/access/file-sharing-lock-count-exceeded

Say to look here:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE
It does not exist, or I can't seem to locate it.

Did you also try to look for this one?

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\x.0\Access Connectivity Engine\Engines\ACE

 

[Thales750]

Did you also try to look for this one?

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\x.0\Access Connectivity Engine\Engines\ACE

No sir, I thought that was for 64 bit. I'll try.

 

[theDBguy]

No sir, I thought that was for 64 bit. I'll try.

Couldn't hurt...

 

[isladogs]

Wow6432Node is for 32-bit Access in 64-bit Windows

 

[arnelgp]

without messing with Registry, you can set it in your db.

Public Function fncMaxLocks(Optional Byval lngLocks As Long = 1000000)
dbEngine.SetOption dbMaxLocksPerFile, lngLocks
End Function

you can create an Autoexec macro and Call the function (RunCode).

 

[Thales750]

At the customer location this database is running on SQL Server. so I'm betting they will not experience this problem. After moving this 200 table database to SQL Server i will never build another one in ACE.

 

[Thales750]

It turns out this is not actually about the Registry. I haven't looked yet, but there is code causing this. Maybe a loop that is creating tens of thousands of locks. We'll see.

 

[The_Doc_Man]

If so, this is a case where life follows art, more specifically the book Everything I Need to Know, I Learned in Kindergarten. And one of those early life lessons was "if you open it, close it." But also, if there is an explicit file-open going on, besides using the Close # command, also look at these commands to help with releasing file locks:

Lock, Unlock statements (VBA)

Office VBA reference topic

docs.microsoft.com