MaxLockPerFile

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

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
[ fanpages ]IT Services ConsultantCommented:
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

?
0
mlcktmguyAuthor 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

[ fanpages ]IT Services ConsultantCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
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.
0
mlcktmguyAuthor 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.
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Glad it was resolved relatively quickly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.