MaxLocksPerFile

Hi,

We're sometimes getting the infamous "maxlocksperfile" error while executing queries like the following on a Microsoft Access database:
strSQL = "ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] nvarchar(70) NULL"
MyADOConn.Execute strSQL

The exact error is "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry", number -2147467259

We're using ADO as part of MDAC 2.5 and have installed Jet SP 8. "MyTable" has about 100,000 records and the current definition of "MyColumn" is nvarchar(50) NULL. Current MaxLocksPerFile is default (9,500).

I know I can workaround this problem by increasing the MaxLocksPerFile registry setting for the Jet 4.0 driver, but I don't know why it's bothering in the first place. At the point of executing the query, I have already dropped several relations (FK's), dropped PK's, recreated PK's and recreated relations. All on tables with about 100,000 records. Everything goes fine, up to the point where it tries to increase the max field size of some column where it gives this error.

It seems that Access is locking each record, but I wonder why it does that. Is there a way that I can instruct Jet/ADO not to lock each record or lock the entire table in stead, so that only one lock is needed?

Please advice, but note that I already know a workaround. The idea of this question is mere to get answers on why and how Jet/ADO locks a database and wether there are ways to instruct Jet/ADO differently.


Thanks in advance,

Luc DerckxStart Free Trial