MaxLocksPerFile problem

[RD]

Hi there,

This is the first time I've ever run into this. I've looked all over and all I
can find are suggestions to edit the registry to increase the MaxLocksPerFile
value from the default (9500) to whatever it is that I need. Examples typically
cite 200,000. Unfortunately, I am altering over 800,000 records and each record
that gets altered generates a file lock and each file lock takes up 2 bytes. I
haven't actually tried it yet but I suspect my box doesn't have enough RAM to do
this. I've put in a request for more RAM but who knows when they'll get around
to it.

In the mean time, I'm wondering if there isn't some way to make the Jet engine
release locks after a record has been altered? Think that would involve closing
and re-opening the recordset? I hope not ... sounds like a PITA.

Any fresh ideas appreciated,
RD

 

[RD]

Ok ... two things ... first, as I suspected, increasing the MaxLocksPerFile
registry value just ran me into memory problems. Second, I tried my own idea of
trapping for the error, closing the recordset and re-opening it. That works but
I'm still wishing there was some way to force Jet to release record locks when
I'm looping through these huge tables. Maybe this is the only way?

Regards,
RD

 

[Guest]

I have the same problem but I am already opening and closing the table
between iterations... I have 4GB memory and increasing the MaxLocksPerFile to
2,000,000 doesn't work either... Have you found any other method from any
other source that can mitigate this issue? the file I am updates has
1,000,000 + records in it and it is growing...

 

[Dirk Goldgar]

Hi there,

This is the first time I've ever run into this. I've looked all over
and all I can find are suggestions to edit the registry to increase
the MaxLocksPerFile value from the default (9500) to whatever it is
that I need. Examples typically cite 200,000. Unfortunately, I am
altering over 800,000 records and each record that gets altered
generates a file lock and each file lock takes up 2 bytes. I haven't
actually tried it yet but I suspect my box doesn't have enough RAM to
do this. I've put in a request for more RAM but who knows when
they'll get around to it.

In the mean time, I'm wondering if there isn't some way to make the
Jet engine release locks after a record has been altered? Think that
would involve closing and re-opening the recordset? I hope not ...
sounds like a PITA.

Any fresh ideas appreciated,
RD

Could you post the code you're using? I've never run into this problem,
and I've updated some pretty big tables. Are you looping through a
recordset and updating each record? If so, is that an operation that
could be performed by a single update query instead?

 

[david epsom dot com dot au]

In the mean time, I'm wondering if there isn't some way to make the Jet

release locks after a record has been altered? Think that would involve

Application.dbengine.idle 1

(david)

 

[RD]

This is what I'm doing right now:

If Err.Number = 3052 Then 'Trap for MaxLocksPerFile error
Set rs = Nothing 'Close recordset
Resume WorkAround 'Clear error and redirect to re-open recordset
End If

"WorkAround" is a label after which I set the recordset. It seems to work fine.
I just wish I didn't have to do it this way.

 

[RD]

Could you post the code you're using? I've never run into this problem,
and I've updated some pretty big tables. Are you looping through a
recordset and updating each record? If so, is that an operation that
could be performed by a single update query instead?

Thanks for the response, Dirk.

As a matter of fact, I have another operation that I *do* use a simple update
query for. That one breaks ("There is not enough memory ... etc.") on only the
largest table. That one I break into smaller chunks based on date. It would be
nice if I could do that with all the tables.

I've looked up these errors on MS's site and they all seem to be caused by the
same things: MaxLocksPerFile and lack of memory.

Now, with this particular operation, I have to loop through the records in code.
The alterations I'm making can't be done with a query. There's just too much
logic involved in figuring out exactly *how* the data needs to be altered.
OTOH, it could be that I'm just better at VBA than SQL.

The Jet engine puts a lock on the file every time you change something. When
you install Access the Max~File setting is set to a default of 9500. When I
Googled MaxLocksPerFile I got a lot of hits so, apparently, it's a common
problem (although, in 10 years of Access developement this is the first time
I've encountered it). The concensus seems to be to edit the registry to
increase the value, with the number 200,000 being mentioned most often.
However, most of those suggestions also carry the caveat of increasing system
memory.

The only thing I've found, so far, is that closing the recordset clears the
Max~File. So, I used this workaround:

If Err.Number = 3052 Then 'Trap for MaxLocksPerFile error
Set rs = Nothing 'Close recordset
Resume WorkAround 'Redirect to re-open recordset
End If

"WorkAround" being a label just before I set the recordset.
This seems to work pretty good although some of the tables take quite a bit of
time to update.

So, now that I have it working, the question comes down to, "Does there exist a
function or single line of code that will force Jet to release record locks
during an operation that alters records?"

If not, oh well. My work around functions just fine.

Best regards,
RD

 

[RD]

Application.dbengine.idle 1

(david)

Hmmm ....

Ok, I've read the help file on this method but I'm not sure I quite understand
what it's doing or even if it's doing the (w)right thing (pun intended).

From the help file:
"Suspends data processing, enabling the Microsoft Jet database engine to
complete any pending tasks, such as memory optimization or page timeouts
(Microsoft Jet workspaces only)."

And:
"Usually, read locks are removed and data in local dynaset-type Recordset
objects are updated only when no other actions (including mouse movements)
occur. If you periodically use the Idle method, Microsoft Jet can catch up on
background processing tasks by releasing unneeded read locks."

Can you explain how this works and how it applies to the issue?

Clarification *much* appreciated.

Thanks,
RD

 

[david epsom dot com dot au]

You are running out of locks, and/or memory for locks.
Access releases locks, and memory for locks, as a background
task.

To give this background task higher priority, make it a
foreground task, by explicitly telling the dbEngine to IDLE,
and to FREE LOCKS while idling.

As to if it actually helps .... try it and then
let us know.

(david)

 

[Dirk Goldgar]

As a matter of fact, I have another operation that I *do* use a
simple update query for. That one breaks ("There is not enough
memory ... etc.") on only the largest table. That one I break into
smaller chunks based on date. It would be nice if I could do that
with all the tables.

I've looked up these errors on MS's site and they all seem to be
caused by the same things: MaxLocksPerFile and lack of memory.

Now, with this particular operation, I have to loop through the
records in code. The alterations I'm making can't be done with a
query. There's just too much logic involved in figuring out exactly
*how* the data needs to be altered. OTOH, it could be that I'm just
better at VBA than SQL.

The Jet engine puts a lock on the file every time you change
something. When you install Access the Max~File setting is set to a
default of 9500. When I Googled MaxLocksPerFile I got a lot of hits
so, apparently, it's a common problem (although, in 10 years of
Access developement this is the first time I've encountered it). The
concensus seems to be to edit the registry to increase the value,
with the number 200,000 being mentioned most often. However, most of
those suggestions also carry the caveat of increasing system memory.

The only thing I've found, so far, is that closing the recordset
clears the Max~File. So, I used this workaround:

If Err.Number = 3052 Then 'Trap for MaxLocksPerFile error
Set rs = Nothing 'Close recordset
Resume WorkAround 'Redirect to re-open recordset
End If

"WorkAround" being a label just before I set the recordset.
This seems to work pretty good although some of the tables take quite
a bit of time to update.

So, now that I have it working, the question comes down to, "Does
there exist a function or single line of code that will force Jet to
release record locks during an operation that alters records?"

If not, oh well. My work around functions just fine.

Have you read this KB article?

http://support.microsoft.com/default.aspx?scid=kb;en-us;286153
You may receive a "There isn't enough disk space or memory" error
message
when you perform an operation on an Access table

As an alternative to editing the registry, it suggests using
DBEngine.SetOption to temprarily override the registry setting for
MaxLocksPerFile.

Also, the information I've found about this error suggests to me that it
only happens when a transaction is in effect. Is that what's happening
here? Is your update taking place inside a transaction? If so, could
you forego the transaction?

 

[RD]

Yup. I commented out my method in the error handler and put
Application.DBEngine.Idle dbRefreshCache
in the outer loop just before my Next i statement. Worked like a charm.

Thanks a lot!

Regards,
RD

 

[chadnash]

THANKS DAVID

err:
If err.number = 3052 Then
Application.DBEngine.Idle 1
Resume
End If

Worked for me

Chad