Maxlockperfile error - Windows 10 (64bit) / Access 2016

[Numpty75]

Hi, I am trying to import 35000+ files from Lotus Approach Database into MS Access Database. I have learned how to build my Access Database and I am following the training process I learned and it all seems in order. However, I am receiving this error message 'Maxlockperfile Exceeded' error. I have followed the common instructions on increasing the Decimal value from 9500 to 100000 and am still getting the error.


Is this simply a case of keeping increasing the value until it works, or is there another issue I need to look at? For example, do I need to import 'x' amount of files at a time and append them as I import more?
The common response of increasing the value, does not seem to work and no one appears to have taken the resolution one step further; I would really appreciate some guidance, please?
I have spent 23 years building the Approach database as it was cheap and easy but very basic, and I am not able to re-enter it all manually.

[moke123]

Assuming your using a loop of some sort, have you tried putting a delay in your loop to allow the db engine a chance to catch up on releasing locks?

[Numpty75]

Hi moke123, I've don't know what the loop is, so I will research it and update the value with a delay when I understand what I need to do. This gives me something to try, so thank you. I will let you know if I get any queries or it if was successful etc., if you don't mind. Thank you.

[isladogs]

Do you mean you are trying to import 35000+ records?
If that is the case, Access should have no issues doing so without increasing the MaxLocksPerFile setting
I have never needed to increase the value above 20,000 even when importing over 2 million records.

You may need to review your import code to see if it can be improved and/or break down the import into 'chunks'
What exactly are you doing when the MaxLocksPerFile error occurs?

There is another similar property called MaxBufferSize that may be worth looking into. See https://www.access-programmers.co.uk...axlocksperfile
I modified that to try & solve a problem importing a huge JSON file of 150+ MB into Access

As for the delay idea, it may be enough to use DoEvents or use the Sleep API call between each 'chunk' of the import