File Sharing Locks Exceeded (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:36
Joined
May 21, 2018
Messages
6,270
I have a large table and I read through the recordset. I am numbering groups of information based on the records in between the groups. I get a File Sharing Locks Exceeded. Can anyone explain this error and why?
Code:
Private Sub UpdateKeyWords()
 Dim rs As DAO.Recordset
 Dim KeyWord
 Dim FoundWord As String
 Dim i As Long
 i = 1
 Set rs = CurrentDb.OpenRecordset("Select * from tblAllWords Order by WordID")
 Do While Not rs.EOF
   If rs!IsStop Then
     i = i + 1
   Else
     rs.Edit
     rs!keywordid = i
     rs.Update
     If rs!isBreak Then i = i + 1
   End If
   rs.MoveNext
 Loop
End Sub
Thanks.
 

Ranman256

Well-known member
Local time
Today, 02:36
Joined
Apr 9, 2015
Messages
4,094
there is no reason to loop thru a record set (slow) when you can run 1 update query. (fast)
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:36
Joined
Sep 21, 2011
Messages
10,342
I was waiting for Doc to chip in :)
Perhaps surround the update with a transaction?

 

LarryE

Active member
Local time
Yesterday, 23:36
Joined
Aug 18, 2021
Messages
151

Gasman

Enthusiastic Amateur
Local time
Today, 07:36
Joined
Sep 21, 2011
Messages
10,342
I had never heard of this, but from what I could find out, it occurs in a multi-user environment when updating transactions but that does not appear to be the case here. Apparently, you need to edit the registry to fix it. Here are some links:
Fix File sharing lock count exceeded error in Office Access (thewindowsclub.com)
Can't process transactions in multi-user environment - Office | Microsoft Docs
No, the link states you can also raise the limit for that process only via code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:36
Joined
Feb 28, 2001
Messages
22,556
First, a question: Does it occur quickly or can you at least figure out the value of "i" at the time of of the error? Can you estimate what percent of that table you process?

Second, the rs.Update or the rs.Edit will be the actual source of that problem because Access is taking out a record-level lock. I'm betting it is the .Edit but not willing to make it a big bet.

It occurs to me that you might be able to use a sub-query to do at least some of this (though the "extra counts" for IsStop or IsBreak might be tricky in a query.)

It is also possible that you merely need to raise your process quota / limit (see Minty's link). If that DOESN'T work, you might need to do some other gyrations of a more extreme nature.

Explanation: If I am correct this is actually a Windows-based file lock. A file lock is just a data structure used by Windows to remember that you are working on something in a file. It takes up space in a shared area in Windows. You might be running into certain system quotas, because file locks are part of the O/S "scratchpad" (a.k.a. "pool") area. Some of the memory quotas are listed here:


I suspect this is either paged pool or non-paged pool.

Since you have an "order by" key in the recordset, maybe you could try something like this:

1. Toss in a couple of variables in that routine to remember the value of I and of WordID field after each successful .Update (actually, you already have "i" but you need to remember "WordID" as well.)

2. Toss in an error trap to a routine that, if you get that particular RTE, you set a flag and resume in an alternate path that closes the recordset and sets it to Nothing. Then allow a .DoEvents to give the system time to actually clean out its stuff. Then loop back almost to the top.

3. At the top, BEFORE you open the recordset, reset those two new variables. THEN have the loop target (from #2 above) and open the same recordset. But now, if you have a non-blank/non-zero I and WordId, do a .FindFirst for the saved WordID, after which you can resume the loop. Then you exit the loop based on whatever else you need as criteria for doing so.
 
Last edited:

LarryE

Active member
Local time
Yesterday, 23:36
Joined
Aug 18, 2021
Messages
151
And make sure you do a rs.Close at the end of your routine. After the Loop.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:36
Joined
May 21, 2018
Messages
6,270
there is no reason to loop thru a record set (slow) when you can run 1 update query. (fast)
Thanks. Did not think of that. Any chance you can help with the SQL for the update.
Example
WordIDWordTextIsStopKeyWordID
1​
wasYes
2​
calculatedNo
3​
basedNo
4​
onYes
5​
theYes
6​
totalNo
7​
appliedNo
8​
maintenanceNo
9​
timeYes
10​
allocatedNo

So wherever there is a break I want to increment the count.
WordIDWordTextIsStopKeyWordID
1​
wasYes
2​
calculatedNo
1​
3​
basedNo
1​
4​
onYes
5​
theYes
6​
totalNo
2​
7​
appliedNo
2​
8​
maintenanceNo
2​
9​
timeYes
10​
allocatedNo
3​
 

Users who are viewing this thread

Top Bottom