Link to home
Start Free TrialLog in
Avatar of jbakestull
jbakestull

asked on

Access Error 3052 Increase MaxLocksPerFile registry

On the rst.Edit line I get the error :-

 Run time error 3052.
 File sharing lock count exceeded. Increase MaxLocksPerFile registry
 entry.

Sub InsertRank()
  Dim sSQL As String
  DBEngine.SetOption dbMaxLocksPerfFile, 2000000
  sSQL = "SELECT * FROM TblCllientEntryRank ORDER BY [Client Uid], [Entry Exit Entry Date] ASC"

  Dim rs As Recordset
  Set rs = CurrentDb.OpenRecordset(sSQL)
 
  Dim iLastID As Long, i As Long
  Do Until rs.EOF
    rs.Edit
    If rs("Client Uid") <> iLastID Then i = 1
    iLastID = rs("Client Uid")
    rs("Rank") = i
    rs.Update
    rs.MoveNext
    i = i + 1
  Loop
 
End Sub

How do I increase Increase MaxLocksPerFile ?
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jbakestull,

IIRC, the default MaxLocksPerFile setting is around 10,000 (ten thousand)

According to your post, you are are already setting yours to 2,000,000 (2 million)!
:-O
Like MX, I have seen typical values as high as 25,000 to 50,000, but never as high as your value.
(typically, you slowly incrementally increase the value until the error just barely stops, then round up slightly)

Remember, increasing this value excessively will have negative consequences elsewhere,  so you can't simply increase this value indefinitely.

For example:
You can "increase" your physical performance by increasing your Caffeine intake, quadrupling you protein intake, Training harder, tacking anabolic steroids, ...etc,  but doing to much of any of those things causes "negative" consequences (High Blood pressure, ketone buildup, less time with family, sexual side effects, ...respectively)

This analogy is probably also true for setting the MaxLocksPerFile to "excessively" high levels...
(Low memory available to other processes/apps, Low system resources, ...etc)

If you are already at 2 million and you are still getting MaxLocksPerFile error, then it is time to take a closer look at this process you are running...

How many records are we dealing with here?
Are users accessing these records while the code is running?
...Throw a counter variable in your code and increase it by 1 for each loop.  Then see how many records were processed before the error.

JeffCoachman
Wow ... I completely MISSED you were already setting this in VBA, sorry.

mx
jbakestull ,

So what was the actual solution here?

I seems like you were already doing what MX stated.