"File sharing lock count exceeded"

[Sheryl]

I'm doing something which should be simple, namely a loop
through a table and updating one field via .Edit, .Update.
When there's a huge number of records, I get the following
message "File sharing lock count exceeded. Increase
MaxLocksPerFile registry entry." Here's the code. How do
I fix it?

Set rs = CurrentDb.OpenRecordset(sQry, dbOpenDynaset,
dbConsistent, dbOptimistic)
If rs.EOF Then GoTo CC_NEXT_CAT

count = 0
rs.MoveFirst
CC_NEXT_PERCENTILE:
count = count + 1
rs.Edit
rs!percentile = 100 * (count / total)
rs.Update
rs.MoveNext
If Not rs.EOF Then GoTo CC_NEXT_PERCENTILE

 

[Wayne Morgan]

You don't mention your version of Access, here is a KB article that may
help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;815281&Product=acc2000

Also, you don't have all of your code here, so there may be something you're
doing that I don't see that is causing the problem. One thing I would
probably change is here:

CC_NEXT_PERCENTILE:
count = count + 1
rs.Edit
rs!percentile = 100 * (count / total)
rs.Update
rs.MoveNext
If Not rs.EOF Then GoTo CC_NEXT_PERCENTILE

Do Until rs.EOF
count = count + 1
rs.Edit
rs!percentile = 100 * (count / total)
rs.Update
rs.MoveNext
Loop

Where is CC_NEXT_CAT located? If it is right after the loop above, then I
would change the statement

If rs.EOF Then GoTo CC_NEXT_CAT

If Not rs.EOF Then
'place the loop here
End If

This gets rid of the GoTo. If you are not at EOF then proceed, otherwise
(EOF = True), skip to the end of the If statement. You are also doing a
MoveFirst before you check to see if there are any records in rs. If there
are no records, then BOF and EOF will both be true. This would make what you
have look like:

If Not (rs.BOF And rs.EOF) Then
Count = 0
rs.MoveFirst
Do Until rs.EOF
count = count + 1
rs.Edit
rs!percentile = 100 * (count / total)
rs.Update
rs.MoveNext
Loop
End If