Run-time error '3052': File sharing lock count exceeded. 1

[kjv1611]

I posted a question earlier, thread705-848586 , where CajunCenturian offered some help in speeding up some code for searching and editing a large table. The following code is the code I ended up with:

Private Sub CountPayments()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCount As Integer, I As Integer, lngAccount As Long
    Dim MyDate As Date
    MyDate = "5/24/2004"
    
    Set db = CurrentDb()
'    Set rs = db.OpenRecordset("SELECT * FROM tblHistoricalbyBatch " & _
'        "WHERE UpdateDate = #" & Date & "#;")
        
    
    With rs
        If rs.BOF And rs.EOF Then
            MsgBox "No records in this file"
        Else
        .MoveFirst
        lngAccount = 0
        Do While Not .EOF
            intCount = 0
            For I = 7 To 12
                intCount = intCount + Abs(Sgn(Nz(rs(I), 0)))
            Next
            .Edit
            .Fields("#Payments") = intCount
            .Update
            .MoveNext
            lngAccount = lngAccount + 1
        Loop
        End If
    End With
    Set rs = Nothing
    MsgBox lngAccount & " Accounts were updated."    
End Sub

The full error message I was given was this:
Run-time error '3052':
[BLUE]File sharing lock exceeded. Increase MaxLocksPerFile registry entry.[/BLUE]

The line of code that is highlighted when choose 'Debug' is:
[GREEN].Edit[/GREEN]

Also, here is what the helpfile says about the error:

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. (Error 3052)
You have exceeded the maximum number of locks allowed on a recordset. This limit is specified by the MaxLocksPerFile setting in your system registry. The default value is 9500, and can be changed either by editing the registry with Regedit.exe or with the SetOption method.

Some other factors that may cause an application to reach this threshold include the following:

amount of available memory
size of rows in the recordset
network operating system restrictions

My work computer is this:
Intel Pentium 4 @ 2.4Ghz (or 2.2Ghz)
1024 MB DDR SDRAM - not sure of the memory speed, pbbly PC266
64mb video card
40 Gig hard drive
Is on a Windows 2000 server system, I believe - don't think upgraded to 2003 yet.
Operating system is Windows 2000
Using Access 2002 w/VBA - no stand alone VB application.



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[flyover789]

Dear kjv1611,

please let me out of this thing.
I'm not able to help you at this place anymore.

Thanks,
Kind regards,

Martin Serra Jr.

http://www.mserrasystems.de

 

[CajunCenturion]

The reason that the original code did not generate any errors is because most of the records did not require any update, and so while you were looping through records that did NOT require updates, Access was releasing some of the locks of records previously updated.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[kjv1611]

Thanks for the attempt... but would you believe I still got the same error when using the date 5/25/2004, which was the most common date in the file?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Ahhh.. I see. Would there be a way to kind of work the two together, so that Access would kind of "catch" and "release" so many records at a time, and thus not reach the record lock limit?

- sorry if my terminology is kind of corny, and not quite as specific/detailed as some, but sometimes I don't remember the correct VBA or Access terms for a particular property, function, or whatever else. [SHADEHAPPY]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[CajunCenturion]

You could, perhaps after every 100 updates or so, issue a DoEvents command. I would experiment with the value of 100 trying to find the largest possible value, something like this:

Do until rs.EOF
   intCount = 0
   For I = 7 To 12
      intCount = intCount + Abs(Sgn(Nz(rs(I), 0)))
   Next
   rs.Edit
   rs.Fields("#Payments") = intCount
   rs.Update
   lngAccount = lngAccount + 1[COLOR=blue]
   If ((lngAccount Mod 100) = 0) then
      DoEvents
   EndIf[/color]
   rs.MoveNext
Loop

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[kjv1611]

flyover789,
You won't hurt my feelings a bit. If you are unable to assist, no problem. Thanks for the effort anyway.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Thanks, Cajun.. could you explain a bit (if possible) about the DoEvents, as to what I was need to do? Would I need to make it empty the records, or just go back to an earlier point in the code? Anyway, have a good weekend, I'm heading out for now. I'll check back up on it and work on it some during the next couple weeks - will probably be a week or so before can get full back into coding mode - other responsibilities... will try asap, though.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[CajunCenturion]

I can't explain it any better than the manual can. Thank you, and you too have a good weekend.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[kjv1611]

Thanks.. I'll take another run at it probably in a week or two... I'd like to now, but need to get some other things finished up first.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sfm6s524]

I had this same problem, did a search here, and found this thread. Basically used code similar to CajunCenturion's second to last sample code, but:

.MoveLast
lngAccount = .RecordCount
maxLocks = lngAccount + 100 ' Add 100 as an extra buffer.
If (maxLocks > 9500) Then ' Don't change it to less than the default
DAO.DBEngine.SetOption dbmaxlocksperfile, maxLocks
End If
.MoveFirst

Worked for me.

Ken

 

[NNewton56]

I was getting the same error message, user the code from CajunCenturion's post and it worked perfectly.

Thanks for the help

.MoveLast
lngAccount = .RecordCount
maxLocks = lngAccount + 100 ' Add 100 as an extra buffer.
If (maxLocks > 9500) Then ' Don't change it to less than the default
DAO.DBEngine.SetOption dbmaxlocksperfile, maxLocks
End If
.MoveFirst