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]

try the following

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 & "#;")
        
rs.movelast   'fills the recset
   
        If rs.recordcount > 0 Then
        rs.MoveFirst
        lngAccount = 0
        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
            rs.MoveNext
            lngAccount = lngAccount + 1
        Loop
        Else:
        MsgBox "No records in this file"
        End If
    End With
    rs.close
    Set rs = Nothing
    db.close
    set db = nothing
    MsgBox lngAccount & " Accounts were updated."    
End Sub

HTH,
fly



Martin Serra Jr.

http://www.mserrasystems.de

 

[kjv1611]

Thanks.. I'm about to try and see if that fixes it, but in the mean time.. here is the actual code I used (forgot and left testing edits in):

Private Sub CountPayments()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCount As Integer, I As Integer, lngAccount As Long
    
    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

It's mostly the same, just no MyDate variable, using Date instead.

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]

just examining a little further:

where do you use your variable

Dim MyDate As Date
MyDate = "5/24/2004"

????




Martin Serra Jr.

http://www.mserrasystems.de

 

[kjv1611]

[WINK]
Looks like I posted the correction just before you posted your question..

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]

You can use the same, as I posted before, should work.

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[kjv1611]

I tried your code with editing the SQL statment to choose the most used date (5/25/2004), and got the same error message as originally posted.

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]

try the following:

in your mdb go to tools->options
select the "advanced" tab
under "default record locking" check "no locks"
uncheck the box "Open databases using record-level-locking"


HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[kjv1611]

Did.. still get same error message..


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]

try thid code:

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 & "#;")
        
rs.movelast   'fills the recset
   
        If rs.recordcount > 0 Then
        rs.MoveFirst
        lngAccount = 0
        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
            rs.MoveNext
        Loop
        Else:
        MsgBox "No records in this file"
        End If
    End With
    rs.close
    Set rs = Nothing
    db.close
    set db = nothing
    MsgBox lngAccount & " Accounts were updated."    
End Sub

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[kjv1611]

Same error in same location of code... boy, this seems to be a toughy...
By the way, I also tried building a query, and taking the SQL code from the query, and using it:

    Set rs = db.OpenRecordset("SELECT tblHistoricalbyBatch.* " & _
        "FROM tblHistoricalbyBatch " & _
        "WHERE (((tblHistoricalbyBatch.UpdateDate) = #5/25/2004#));")

But also still got same message.

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]

I did also forget to mention that the Query ran fine, when built in Query Design view, but that this code here seems to be giving the message. Is there some other way it could be accompllshed without creating this error?

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]

flyover789 - Not being a DAO expert, I'm interesting in knowing exactly what you are suggesting that kjv1611 do, and how those suggestions might affect the "File sharing lock count exceeded" error?

Also, could you please explain, in terms of the DAO environment, the impact, especially in multi-user applications, of turning off the default Access locking mechanisms? I can see where this might solve this particular problem, but at what price?

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

 

[flyover789]

sorry, I didn't read the full thread of your initial post (more than 40 posts... ufff)

just let me know, what you want to accomplish with the following:

intCount = 0
For I = 7 To 12
intCount = intCount + Abs(Sgn(Nz(rs(I), 0)))

HTH,
fly


Martin Serra Jr.

http://www.mserrasystems.de

 

[CajunCenturion]

I've been doing some research into this particular problem, and have come across a possible registry setting fix, but that doesn't solve the problem, only increases the record locking limit, in which the default setting is 9500 locks. In Novell environments, you cannot increase that limit, because of Novell restrictions. But even if not in a Novell environment, increasing the limit to 12,000 may allow the error to go away today, but tomorrow, when the update set is over 12,000, the problem will reappear.

There is also a possibility that due to the extrememly high number of record updates being perform, you might need to add a delay so as to allow Access sufficient time to release some of the record locks, but I cannot as of yet, confirm this.

Still researching.

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

 

[flyover789]

@CajunCenturion

I'm just trying to help this guy out to get his func running.

This thread is not a discussion about database security issues.

kind regards,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[CajunCenturion]

There is nothing wrong with the function. This issue is the number of records being processed and updated.

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

 

[kjv1611]

I would gladly email you the entire database for review, but that would not be good, since it has actual account info in some of the tables (how much, I'm unsure of), but could be illegal when dealing with privacy laws allthesame.

I could just explain, however, what it is doing, and maybe "create" a sample db of it with fake data to fiddle with... I'll try to do that in the mean time, but here is the whole purpose/plan:

Currently, the data analyst using this database has to do a good bit of manual work to count the number of payments from month 7 to month 12 for each account. The entire purpose for this module was to take the table, (tblHistoricalbyBatch), and count the total number of payments posted to each and every account, then post that Count in the #Payments field. The fields in the table include these:
Field Name Data Type
_________________________________________

DEBTOR Text
acctnumber Text
acctid Number
1 Currency
2 Currency
3 Currency
4 Currency
5 Currency
6 Currency
7 Currency
8 Currency
9 Currency
10 Currency
11 Currency
12 Currency
BatchID Number
#Payments Number
UpdateDate Date/Time


There are currently 61518 records in the table. That number will only grow as time goes on, so it's not getting any smaller.

The data for the table is actually pulled from a large network-based database which stores the minute details for each account. This table is primarily for storing the payment history (amounts) for the past 12 months on each account in a particular batch(group) of accounts. I was attempting to just create some code that would count the payments for each account with the push of a button, or in a load() or afterupdate() type argument.
The query which I built that would pull the information by date is the SQL code:

SELECT tblHistoricalbyBatch.*
FROM tblHistoricalbyBatch
WHERE (((tblHistoricalbyBatch.UpdateDate)=#5/25/2004#));

And actually, I just thought - would I be able to build the counting part into a query, and just copy the sql code for it all into the VBA module? I'm a novice at all of this, and just taking a shot at different small projects as they come along, while working on one pretty big project for my own department (Auditing).

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]

The delay sounds good to me (worth a try). How would that be accomplished. And by the way, here is my original code (without the date part) that worked slowly, but did not give any errors - takes about 15-30 seconds on my machine, but over 5 min's on the Celeron (user's) machine.

Original code without the date part:

Private Sub Count()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblHistoricalbyBatch")
    Dim intPaymentCount As Integer, I As Integer, lngRecordCount As Long
    
    rs.MoveFirst
    lngRecordCount = 0
    Do While Not rs.EOF
        intPaymentCount = 0
        With rs
            I = 7
            For I = 7 To 12
                If rs(I) <> 0 Then
                    If rs(I) <> "" Then
                        intPaymentCount = intPaymentCount + 1
                    End If
                End If
            Next
            .Edit
            .Fields("#Payments") = intPaymentCount
            .Update
            .MoveNext
        End With
    Loop
lngRecordCount = rs.RecordCount
MsgBox lngRecordCount & " Accounts were updated."
Set rs = Nothing
End Sub

The main problem with this code was the speed, and I had not even added in the argument for comparing the date.

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 may have come up with something. I've come across some references that allow for a temporary increase of this lock limit using the SetOption command. This has a side added benefit that you can remove a counter. Unfortunately, I am not in a position to test this, so I make no guarantees, but it might be worth a shot.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCount As Integer, I As Integer, lngAccount As Long
[COLOR=blue]Dim maxLocks as Long[/color]
    
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM tblHistoricalbyBatch " & _
        "WHERE UpdateDate = #" & Date & "#;")
        
With rs
   If .BOF And .EOF Then
      MsgBox "No records in this file"
   Else[COLOR=blue]
      .MoveLast
      lngAccount = .RecordCount
      If (lngAccount > 9500) Then [COLOR=green]' Don't change it to less than the default[/color]
         maxLocks = lngAccount
         DAO.DBEngine.SetOption dbmaxlocksperfile, maxLocks
      End If
      .MoveFirst[/color]
      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
      Loop
   End If
End With

MsgBox lngAccount & " Accounts were updated."
Set rs = Nothing
Set db = Nothing

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