MaxLockPerFile

mlcktmguy
mlcktmguy used Ask the Experts™
on
I have a looping routine that passes thru a file of about 300,000 records.  The routine itself is very simple but I am encountering  error -2147217887 (maxlocksperfile exceeded) when I process about 35,000 records.
I can get all the records to process by selecting 'Debug' when the error pops up then hitting the continue arrow.  The message comes up every 20,000 records but I eventually get thru them all.

I added the statement
DAO.DBEngine.SetOption dbMaxLocksPerFile, 300000 at the top of the procedure but it doesn't seem to have any effect.

Is there something else I can do to keep the error from occurring.  I've never used Begin and End Transaction but perhaps that would help in this case.  Even though I can get this to process all records by doing the 'Debug' & Continue that only works for me.  I can't turn it over to the users that way.

Here's the procedure:
Public Sub distributeCOPOther()
'
DAO.DBEngine.SetOption dbMaxLocksPerFile, 300000
'
selectString = " Select *  from aaSynch_Step1_Converted Where [Other] <> 0"
'
Dim wkPeriod As Long
Dim wkPeriodMMDD As Long
Dim wkYear As Long
Dim wkPrincipal As Double
Dim wkPenalty As Double
Dim wkInterest As Double
Dim wkOther As Double
Dim wkLien As Double
Dim wkAttyFees As Double
Dim wkYearlyLienCost As Double
'
Dim wkYearThreshold As Double
'
startTime = Now
currTime = Now
'
dispCnt = 5000
dispMax = 300
'
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
If rsIn2.EOF Then
    '
Else
    If rsIn2.RecordCount > 0 Then
        '
        rsIn2.MoveLast
        rsIn2.MoveFirst
        '
        totRecs = rsIn2.RecordCount
        recsRead = 0
        '
        dispCnt = 5000
        dispMax = 500
        '
        While Not rsIn2.EOF
            '
            recsRead = recsRead + 1
            '
      '      If recsRead > 250 Then
      '          Stop
      '      End If
            '
            dispCnt = dispCnt + 1
            If dispCnt > dispMax Then
                dispCnt = 0
                currTime = Now
                dispMsg = "Distributing COP Other Amount, Processing " & Format(recsRead, "Standard") & " Of " & Format(totRecs, "Standard") & RunTime(startTime, currTime)
                wkStatusRtn = SysCmd(acSysCmdSetStatus, dispMsg)
                DoEvents
            End If
            '
            wkPrincipal = Nz(rsIn2!Principal, 0)
            wkPeriod = Nz(rsIn2!Period, 0)
            wkOther = Nz(rsIn2!Other, 0)
            wkPeriodMMDD = Val(Mid(Trim(Str(Nz(rsIn2!Period, 0))), 5, 4))
            wkYear = Val(Mid(Trim(Str(Nz(rsIn2!Period, 0))), 1, 4))
            wkLien = 0
            wkAttyFees = 0
            '
            ' don't do fee records
            '
            If wkPeriodMMDD = 1231 Then
                If wkYear < 2014 Then
                    wkYearlyLienCost = 20
                    wkYearThreshold = 23.6     ' joe's number which is max lien plus atty fees on the lien amount
                Else
                    wkYearlyLienCost = 86.7
                    wkYearThreshold = 102.3
                End If
                '
                ' Per Joe, if Other less than zero put it all in Principal
                '
                If wkOther < 0 Then
                    wkPrincipal = Round(wkPrincipal + wkOther, 2)
                    wkLien = 0
                    wkAttyFees = 0
                Else
                    '
                    If wkOther >= wkYearThreshold Then
                        wkLien = wkYearlyLienCost
                        wkAttyFees = Round(wkOther - wkYearlyLienCost, 2)
                    Else
                        wkLien = Round(wkOther * (1 / 1.18), 2)
                        wkAttyFees = Round(wkOther - wkLien, 2)
                    End If
                End If
                '
                rsIn2!Principal = wkPrincipal
                rsIn2!Lien = wkLien
                rsIn2!AttyFees = wkAttyFees
                '
                rsIn2.Update
                '
            End If
            '
            rsIn2.MoveNext
        Wend
            
    End If
End If
'
rsIn2.Close
Set rsIn2 = Nothing

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Is there a reason you are using ADO rather than DAO?  DAO is the preferred method for interacting with Access data.  And I usually use it for SQL Server as well.
Are you missing the statement:

rsIn2.Edit

Just after the statement:

While Not rsIn2.EOF

...or, at the very least, just prior to this statement:

rsIn2!Principal = wkPrincipal

?

Author

Commented:
Dale: I used DAO in the original versions of Access but switched to ADO for all file handling when it became available since it was supposed to be the 'latest and greatest'.  Would using DAO resolve this issue?  I don't have any DAO examples left, how would this routine differ in DAO.  I know I would have to use the .edit property.

fanpages:  .edit is not required, and maybe not even available, when using ADO.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Thanks mlcktmguy; yes, sorry, I am aware of that.

I edited my response a few times & left out a pertinent word or two referring to re-coding with Data Access Objects, that you worded much better than me in your response to Dale :)

Have you looked at changing the .Update syntax with a simple Recordset/ActiveConnection Execute statement using an SQL UPDATE syntax so that the .Update is not used at all?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
micktmguy,

Other than opening the recordset, use:

Dim rsIn2 as DAO.Recordeset
set rsIn2 = currentdb.Openrecordset(SelectString, , dbfailonerror)

Open in new window

instead of:
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Open in new window

and use of:

rsIn2.Edit

 there would be no difference.  Most Access experts will advise you to use DAO unless you need some of the functionality of ADO (multiple recordsets in single query, disconnected recordsets).  In my 20+ years of using Access, I have very rarely used ADO except to interact with a remote SQL Server.

Author

Commented:
Thanks fanpages, I don't usually use the SQL update when I'm updating records that I'm looping thru. However, in this case using the SQL Update resolved the max locks exceeded issue.

Partial to Dale for giving me a heads up on DAO.
You're very welcome.

Glad it was resolved relatively quickly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial