Make code faster for searching a table for date field, run code... 1

[kjv1611]

I have the following code that works, but is extremely slow, b/c dealing with at least 5000 to 6000 accounts. Also, the number of accounts will grow each time, so the next time, there will be probably 11,000 + accounts, and then 16000 or 17000 accounts total the next time, so I really need to use some kind of VB code or SQL code in order to find the first instance, set the recordset to that record, and do the remaining code for the records that meet the criteria (current date), but only start at the first instance - or whatever will work quickly. Again, current code works, but VERY SLOW!!!

Option Compare Database
Option Explicit

Private Sub Count()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblHistoricalbyBatch")
    Dim intCount As Integer, I As Integer, intAccount As Integer
    
    rs.MoveFirst
    intAccount = 0
    Do While Not rs.EOF
        If rs!DateUpdated = Date Then
            intCount = 0
            With rs
                I = 7
                For I = 7 To 12
                    If rs(I) <> 0 Then
                        If rs(I) <> "" Then
                            intCount = intCount + 1
                            intAccount = intAccount + 1
                        End If
                    End If
                Next
                .Edit
                ![#Payments] = intCount
                .Update
                .MoveNext
            End With
        End If
    Loop

MsgBox intAccount & " Accounts were updated."
End Sub

Any help/advise would be greatly appreciated. I would think that it would be best to use SQL somehow in this to make it quicker, but I'm not exactly a veteran at all of this.

Thanks in advance,

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]

By the way, here is the help file's info on 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

I have 1 GB of DDR memory with a Pentium 4 processor. I wouldn't think it involved that, as it worked fine with the old code...

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]

Well, there are some blank fields in the payment blanks on some accounts, where there are only 0's in others. I think that would be Null, am I correct?

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]

On the Nz thing - I'm glad you advised that, b/c tried w/o, and got the invalid use of null 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]

I would suggest that you start a new thread focused solely on the "File sharing lock count exceeded" Error. Perhaps someone with more recent expertise in DAO, and the OpenRecordSet options and parameters will come to the rescue.

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

 

[kjv1611]

Thanks for the advice.

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