Getting file sharing lock count exceeded error (1 Viewer)

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
I'm trying to write some code to help the forum member on this thread. When I run the code that follows it errors on the line rs.Edit with the error message:



In googling this error all I'm finding is recommendations to change the MaxLocksPerFile in the registry. I'm using Access 2013 and Windows 7 and this entry is set to 9500. That just about where the code quits. After updating about 9400 and some records. But increasing this number isn't a solution as this needs to work with over a million records. I don't understand why the lock on the record isn't release when it updated. What am I missing in this code (database attached)

Code:
Public Sub UpdatePreviousValues()

Dim rs As DAO.Recordset
Dim varPreviousValue As Variant
Dim strPreviousAccount As String
Set rs = CurrentDb.OpenRecordset("qrySourceSorted")
'if there are 0 - 1 records nothing is done
If rs.EOF And rs.BOF Then
    Exit Sub
Else
    rs.MoveLast
    If rs.RecordCount < 3 Then
        Exit Sub
    End If
    rs.MoveFirst
End If
strPreviousAccount = rs!Account
varPreviousValue = rs!Current_Value
rs.MoveNext
Do While Not rs.EOF
    If rs!Account = strPreviousAccount Then
        rs.Edit
        rs!PreviousValue = varPreviousValue
        rs.Update
    End If
    strPreviousAccount = rs!Account
    varPreviousValue = rs!Current_Value
    rs.MoveNext
Loop
rs.Close


End Sub
 

Attachments

  • Prior Record test DB.accdb
    1.5 MB · Views: 161
  • ErrorMsg.png
    ErrorMsg.png
    17.6 KB · Views: 1,526

moke123

AWF VIP
Local time
Today, 09:52
Joined
Jan 11, 2013
Messages
3,852
tried it with this added in and it worked

Code:
rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
DAO.DBEngine.SetOption dbMaxLocksPerFile, x



Code:
Public Sub UpdatePreviousValues()
Dim x As Long
Dim rs As DAO.Recordset
Dim varPreviousValue As Variant
Dim strPreviousAccount As String
Set rs = CurrentDb.OpenRecordset("qrySourceSorted")
'if there are 0 - 1 records nothing is done
If rs.EOF And rs.BOF Then
    Exit Sub
Else
    rs.MoveLast
    If rs.RecordCount < 3 Then
    MsgBox rs.RecordCount
        Exit Sub
    End If
    rs.MoveFirst
End If
strPreviousAccount = rs!Account
varPreviousValue = rs!Current_Value

rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
DAO.DBEngine.SetOption dbMaxLocksPerFile, x

rs.MoveNext
Do While Not rs.EOF
 
    If rs!Account = strPreviousAccount Then
    With rs
        .Edit
        !PreviousValue = varPreviousValue
        .Update
     End With
    End If
    strPreviousAccount = rs!Account
    varPreviousValue = rs!Current_Value
   
    rs.MoveNext
Loop
rs.Close


End Sub
 

JHB

Have been here a while
Local time
Today, 14:52
Joined
Jun 17, 2012
Messages
7,732
Run it direct against the table.
 

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
Run it direct against the table.

Can't do. It needs to be sorted, but the problem doesn't occur when editing the table directly.
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
There's more to this. If you download the database I attached it will probably work ok. I've found that this problem occurs when I make a copy of the table. I made a copy of the table for retesting so I copied in pasted the tabled name Source to Copy of Source. After a test I deleted the table Source and Copied and pasted the Copy of Source to Source. Then the error occurs. So this is just a curiosity now and not a problem.

Thanks for your responses.
 

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
I had it working and tested it several times. This time clearing the column I was updating with an update statement rather than copy and pasting the table. But then I did a compact and repair the the problem is back. moke123's solution fixes it but I'm wondering it that will work with millions of records. Is there a max to the dbMaxLocksPerFile? Also I like to understand why this is necessary.
 

moke123

AWF VIP
Local time
Today, 09:52
Joined
Jan 11, 2013
Messages
3,852
found this explanation

What happens is that with each invocation of .Edit, you claim a lock, then your call to .Update tells the database engine to release that lock. However, releasing of a lock is done asynchronously, which allows your code to continue while the lock is being released. So what happens is that you claim locks with your .Edit faster than the database engine can release them, thus raising the error you see. Keep in mind that with adjusting the setting you still run the risk of raising the 'max locks exceeded' error if your recordset grows to a point that your .Edit's claim locks faster than the db engine can release them. So to ensure you avoid this error, you can put a delay in your loop in order to allow the releases to 'catch up'.
 

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
Putting in a delay in stops the error, but I found I had to adjust the delay to about 500 milliseconds before the error stopped. Introducing a delay of about one half second every 9500 or so records is of course not a solution. I tried putting in a DBEngine.Idle dbRefreshCache as that is supposed to "to release unneeded locks" according to this site but that didn't help. So I put back

Code:
rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
DAO.DBEngine.SetOption dbMaxLocksPerFile, x

Thanks, moke123 for this fix but this still bugs me. I see code all the time on this forum that updates records in a loop like this and I don't recall seeing code in it to increase the MaxLocksPerFile. This doesn't seem natural. Who would expect to have to do this for something so routine? I think I might try ADO and see if I get the same error.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Jan 23, 2006
Messages
15,364
sneuberg,

Just a thought but you might try DoEvents instead of some sort of delay of X milliseconds.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:52
Joined
May 7, 2009
Messages
19,175
moke123 suggestion will work for million of records, providing you flush (actual disk save) the records for sometime, thereby releasing read locks.

DbEngine.Idle dbFreeLocks

...
...
DAO.DBEngine.SetOption dbMaxLocksPerFile, x

rs.MoveNext
dim lngRecCount As Long
Do While Not rs.EOF

If rs!Account = strPreviousAccount Then
With rs
.Edit
!PreviousValue = varPreviousValue
.Update
End With
End If

strPreviousAccount = rs!Account
varPreviousValue = rs!Current_Value

rs.MoveNext
lngRecCount = lngRecCount + 1
'manually release locks every 5,000 records
If lngRecCount > 5000 then
DbEngine.Idle dbFreeLocks
lngRecCount = 0
End If

Loop
rs.Close
 

JHB

Have been here a while
Local time
Today, 14:52
Joined
Jun 17, 2012
Messages
7,732
A DbEngine.Idle dbFreeLocks does not seem to help.
But putting in an errorhandler seems to help, at least by me.

Code:
..
HandelError:
If Err.Number = 3052 Then
  Resume
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:52
Joined
May 7, 2009
Messages
19,175
it's the dbRefrechCache that did not work.
the default dbMaxLocksPerFile is in your registry. this is the figure that msa uses when you open your db.
the figure there is small.
temporarily increasing it, using DBEngine.SetOptions dbMaxLocksPerfile, x
 

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
Thanks for all of your responses


I've attached a database which has a simplified version of this problem with a table with about 200,000 records. This is what I've found in my tests.

  • For some reason moke123's solution doesn't work in this case unless I add some addtional MaxLocksPerFile. I added ten to get that to work.
  • DBEngine.Idle dbFreeLocks doesn't help.
  • JHB's suggestion of resume on error works but increases the runtime of the program from about 10 seconds to 50 seconds.

So the modified moke123 solution seems to be still the best solution.
 

Attachments

  • UpdateRecordsTest.zip
    1.2 MB · Views: 142

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:52
Joined
May 7, 2009
Messages
19,175
tested with dbFreeLocks alone, passed

tested with dbmaxlocksperfile alone, passed.

tested without both, still it passed.

maybe its your computer memory problem.
 

sneuberg

AWF VIP
Local time
Today, 06:52
Joined
Oct 17, 2014
Messages
3,506
tested with dbFreeLocks alone, passed

tested with dbmaxlocksperfile alone, passed.

tested without both, still it passed.

maybe its your computer memory problem.
Does it fail when neither of these are in place? What's MaxLocksPerFile in your Registry set to? If you happen to have Window 7 you can find it at

Code:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

It seems more likely that this is set high on your computer than this being an issue with my computer memory. Also note that JHB stated
A DbEngine.Idle dbFreeLocks does not seem to help.
But putting in an errorhandler seems to help, at least by me.
 

JHB

Have been here a while
Local time
Today, 14:52
Joined
Jun 17, 2012
Messages
7,732
In sneuberg last posted example database an error handler is on, so it will pass.
Else I experience the same as sneuberg.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:52
Joined
May 7, 2009
Messages
19,175
my dbMaxLocksPerfile is 9500.
 

speakers_86

Registered User.
Local time
Today, 09:52
Joined
May 17, 2007
Messages
1,919
Thanks for this. I have this issue too, but never got around to troubleshooting it.
 

Users who are viewing this thread

Top Bottom