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(sS QL)
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 ?
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(sS
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
mx
jbakestull ,
So what was the actual solution here?
I seems like you were already doing what MX stated.
So what was the actual solution here?
I seems like you were already doing what MX stated.
http://support.microsoft.com/kb/815281
http://www.d-toolsblog.com/technical-terminal/file-sharing-lock-count-exceeded-increase-maxlocksperfile-registry-entry/