AutoNumber Error -- MaxLocksPerFile ??

[pbarnes]

I'm getting a unique error when I attempt to add an AutoNumber field to a table that I created. The table itself has ~110,000 records but it will not let me create an ID tag for the records so that I can create a primary key.

The error reads "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry."

If anyone has come across this error before and knows how to get around it please let me know. Thanks!

See attachment for a jpeg of the error.

 

[pbarnes]

I have scrolled through other posts that are somewhat similar to this problem, however, I am not exactly a programmer so when people talk about entering code I'm asking myself -- where?

If I can do it in VB then I can handle it...but I know enough to get nervous when someone is telling me to edit my registry with RegEdit.

 

[Moniker]

MaxLocksPerFile errors happen in transactions exceeding 9500 records, or when you are doing a lot of client/server work. (9500 is the default number of lockable records in Access. Records are temporarily "locked" while they are being updated.)

To change the default, throw this line of code into the Form_Load event of whatever form first appears when your DB opens. (If you don't have an auto-opening form, put it as an AutoExec macro and use a RunCode action.)

Application.DBEngine.SetOption dbMaxLocksPerFile, 1000000

This will temporarily change the MaxLocksPerFile setting in the registry from 9500 to 1000000 (should be way more than enough). That setting will stay in effect until you close Access, at which point it will revert back to 9500. Note that doing this can potentially slow things down a little as Access will reserve memory and/or hard drive space for this. So long as you have 1GB or more of memory, though, you won't even notice the difference.

 

[pam0566]

i was trying to analyze tables in an old / outdated database.. in access 2003 and got the same error ..... i tried what you mentioned.. but still got same error. any other ideas? i want to streamline some of the tables so i can update.. but want to save the query and it wotn let me. we have over 50k records though