File sharing lock count exceeded error 1

[Paladyr]

This occurs when I try to delete an item, and access cascades the deletes through many tables with over 1 million records. I am aware of the registry setting that allows you to bump up the number of records you can lock but this will not be an option b/c:

1. It looks like the max won't be enough to complete this delete.
2. I don't want to have to change everyone's registry settings.

I know I can go through and delete each individual record, but isn't there a way that you can tell access "Hey, I have the database opened exclusively so don't worry about locking every single affected record!!!"??? Thanks for any help you can give... even if it is just saying "Nope, I don't know of a way." Thanks!

 

[CCLINT]

Try gradually increasing the value of this property (default is 9500):

Conn.Properties("Jet OLEDB:Max Locks Per File&quot = 20000 [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[jopaumier]

Paladyr,

Did you solve this problem? I am encountering it and have yet to find a solution. On every record of every table in a database, I am modifying two fields. The first 7 tables have from 70-2500 records and everything works fine, but the 8th has 14511 records, so I am encountering this error at 9497. I thought that the rs.movenext releases the lock, but somewhere I read that the system cannot keep up with the updates and that there is a backlog of updates. I could not get CCLINT's suggestion to work. And I am wondering what will happen when I attempt to modify a table with 1 million records.

Jim

 

[CCLINT]

Try setting the value directly in the registry and see if that works.
The below key is for using the JET provider 4.0 on a JET 4 database:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
Set the value for MaxLocksPerFile to 50000 (double click and click on Decimal value, then type the value in)

 

[Paladyr]

Yea I fixed it, the long way. I just ended up cycling through the records and making the changes that way . Changing the registry just isn't an option when you are trying to let other people run a program that makes mass-updates.

 

[CCLINT]

>Changing the registry just isn't an option

You can do this programically very easily - I've had no problem doing it this way...

 

[Paladyr]

Yea but then you always have to know ahead of time how many locks you need... for me it was just as fast to delete record by record, so it wasn't that big of a deal.