File Sharing lock count exceeded, Increase...

[ilcaa72]

i have 13 million rows, i am trying to do a simple concatenation
[Date]&" "&[Time]

i get error, "File Sharing lock count exceeded, Increase MaxLocksPerFile registry entry

i have increased it from default 9500 (decimals) to 20000. still getting error. Any advice?

this is the location that has entry for MaxLocksPerFile
Windows 7, Access 2016

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0

 

[isladogs]

I believe that's the wrong path and only applies to Access 2000-2003

For use with Access 2016, you should be referencing ACE not Jet

See http://www.anysitesupport.com/access-maxlocksperfile-file-sharing-lock-count-exceeded

Modify the path from that in the link to 16.0 for 2016 version
Wow6432Node only applies if you have 64-bit Windows

For Access 2016 running on Windows 32 bit (x86): 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

For Access 2016 running on Windows 64 bit (x64): 
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

 

[moke123]

I believe you can also set it temporarily without messing with the registry.

see https://msdn.microsoft.com/en-us/library/office/ff836236(v=office.14).aspx

 

[arnelgp]

you can set MaxLocks on code. Before any addition/edits of records execute the command.
you can Use DBEngine.Idle dbRefreshCache to explicitly (force) released lock, see sample:

DBEngine.SetOption dbMaxLocksPerFile, 100000
Do While Not RS.Eof
... update goes here
.MoveNext
Loop
' Call the Idle method to release unneeded locks, force
' pending writes, and refresh the memory with the current
' data in the .mdb/.accdb file.

DBEngine.Idle dbRefreshCache

note that using this method incurs delay penalty. Ofcourse on newer machines this is unnotiecable.
you can further optimized, by creating a counter and releasing locks on specific count, say every 1000 records:

dim i as Integer
i = 1
Do While Not RS.Eof
...update goes here
i = i + 1
if i > 1000 then
' relese locks after 1000 records
DBEngine.Idle dbRefreshCache
i = i + 1
end if
.MoveNext
Loop

Note also that using Transaction cause same effect. Issuing Transaction.Commit explicitly release the locks