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

 

[Mtlca401]

You could try this:

dim lngRecordCount as long, lngCount as long

rs.movelast
lngRecordCount = rs.RecordCount
(This will give you the # of records)

rs.movefirst
(Just to put you back at the beginning of the table)

for lngCount = 1 to lngRecordCount

'code here

next lngCount
(I have found this to cut the speed down a little, )
(but if that's to slow you can try using an SQL
select statement.)

As far as the actual code goes, instead of using:

If rs!DateUpdated = Date Then
try using:

dim strCriteria as string

strCriteria = "'" & rs!DateUpdated & "' = date"

rs.findfirst strcriteria

if rs.nomatch = true then
msgbox "No record found"
else
msgbox "Record found"
end if

let me know if this works better.

 

[Rolliee]

One suggestion is to index the date and do not go thru the file till the end but only while rs.yourdate is less than or equal the test date. Also a "!" does slsow down the code. Try using the rs.fields(NumFieldPos) remembering that it is zero based.

RollieE

 

[kjv1611]

Well, does anyone know of a way to code this in SQL, or is there another forum anyone can recommend that would be good for getting help with SQL code for the SELECT statement? I've used a little on my own, but I emphasise "little".

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

 

[willir]

Stephen

Technically, storing the number of payments breaks a rule of normalization. Instead of storing the number of payments, and adjusting the number through maintenance, consider calculating the number of payments on the form or in the report...

=DCount("[#Payements]", "tblHistoricalbyBatch", "[YourAccountNumFld] = " & me.YourAccountNumFld)

...Moving on
Even if you did store the number of payments, are you confusing "Accounts" for "Records"? Or something else...

I am curios about your code since it seems that you are checking fields rs(7 to 12) to determine number of payments.

Under normalization, one would probably keep the payments in a different table than the account table.

tblAccounts
AccountNum - primary key
+ other account info

tblHistory
HistoryID - PK
AccountNum - foreign key to the account table
+ specific history record, one record for each date

From past experience, effecient coding benefits from effecient design. (Example: Mtlca401 suggestion is assuming your design is correct.

Of course, I may be way off base - I am basing this statement on minimal amount of information.

...or this is not what you wanted to hear...

 

[kjv1611]

willir,
In regards to the Accounts vs records, I am just using the intAccount counter to track how many records are updated. The reason I chose that term, is because each record is a separate account.
I do agree that possibly it would have been best to keep the payments in a separate table, rather than one field per payment in this table, but it's not my database - I was only trying to assist in a counter for the number of payments. The desired affect (from what I was told) was to just have a field show the actual number of payments for each account.
Since you mentioned the DCount function, do you think a combination of using DLookup and DCount could fix the speed problem well enough without resorting to SQL? I need to look at a database of mine, where I've used one or both of those for reference again..

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]

Also, I did not consider this. The table I'm working with is not a table that stores all kinds of information about accounts. It is actually pulled from a separate database system, where all the actual account info is stored. There are only a couple of fields in this table other than the payments 1 - 12. Don't know if that would help any, but it might make more sense knowing this info.

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]

Also... Here is the same code, but cleaned up a little bit (took out the date part, and put the account counter in a different location (not required - only for a msgbox for my info (for now).

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, lngAccount As Long
    
    rs.MoveFirst
    lngAccount = 0
    Do While Not rs.EOF
        intCount = 0
        With rs
            I = 7
            For I = 7 To 12
                If rs(I) <> 0 Then
                    If rs(I) <> "" Then
                        intCount = intCount + 1
                    End If
                End If
            Next
            .Edit
            .Fields("#Payments") = intCount
            .Update
            .MoveNext
        End With
        lngAccount = lngAccount + 1
    Loop
Set rs = Nothing
MsgBox lngAccount & " Accounts were updated."
End Sub

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]

Also, I'm going to look a little further at Mtlca401's code, and see how much difference that makes. Once I took out the if/then stuff for the date, that did refix the time some on my machine (P4 processor), but on the other person's machine (Celeron currently), it takes at least 5 min's as compared to my about 30 sec's on my machine. My machine also has twice the Ram (1Gb as compaired to 512Mb) - just side note info. Of course, I want it to run on the other machine quickly, not just mine. [WINK]

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]

Quick question here: I tried the idea from Mtlca401 above,

Private Sub....
     ~Other Code~
     Set rs = Nothing
     [B][BLUE]Mtlca401's idea - >[/BLUE][/B]lngRecordCount = rs.RecordCount
     MsgBox lngRecordCount & " Accounts were updated."
End Sub

and got the following error message on the line:
"lngRecordCount = rs.RecordCount
Error Message:
Run-time error '91':
Object variable or With block variable not set

Below is my entire code (current), in case need for determining problem:

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 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
Set rs = Nothing
lngRecordCount = rs.RecordCount
MsgBox lngRecordCount & " Accounts were updated."
End Sub

Thanks for anyone's help here..



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]

What is the range of values that you could have in rs(7) through rs(12)? This could help in eliminating the conditional statemetns.

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

 

[Mtlca401]

That might be because you have

set rs = nothing

rs is not set anymore so it doesn't know where to look.

 

[kjv1611]

Woops, color me stupid! [WINK], that was the ticket.. I just had to move the statement set rs = nothing down below the MsgBox statement. Thanks for catching my error!
LOL.. [SMILE]

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]

Cajun,
The fields rs(7-12) have their format set to "Currency" - b/c they are dollar amounts.

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

 

[Mtlca401]

No problem

you do have in there

rs.movelast

because if not then it will never know how many records there are.

 

[kjv1611]

Just a note, it seems my code is currently taking about 15-20 seconds on my machine to update 61,000 records - originally (stupid me) I posted that the table had 6,000 accounts approximately. I was a wee bit off, b/c just glanced at the record count real quick before. Here is the current code:

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 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

There are 2 things I want to be able to fix here. The code does work correctly, updating the correct field of each account, but I still want it better.
1.) I want it to run faster (so does not take the 5 min's
on the other machine).
2.) I want it to check the field labeled [DateUpdated] in
the table, which I have set to the OpenRecordset, rs
in my code, and only update those accounts that have
today's date (which would just be the pre-defined
word/term, Date), not updating records which
have an earlier date (they would already be updated,
and that would be wasteful of system resources.

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]

Hmmm... Mtlca401,

I did not use the rs.movelast statement, but it did give the correct count, b/c left off on the last record. However, if I get this to work only on certain records, I guess I'll need a counter instead. Or can you make the statement, rs.MoveLast, tell how many records work updated with the current run of 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

 

[CajunCenturion]

There are a couple of speed ups that can be made to the loop, namely moving the With to get maximum effect, and removing the conditional statements. I'll post those if you're interested.

My preference would be to try the following SQL Statement, where fld7 through fld12 are the actual fieldnames of these columns, and NumPymts is the actual

UPDATE tblTestData SET #Payments = abs(sgn(nz([fld7], 0))) + abs(sgn(nz([fld8], 0))) + abs(sgn(nz([fld9], 0))) + abs(sgn(nz([fld10], 0))) + abs(sgn(nz([fld11], 0))) +abs(sgn(nz([fld12], 0)));

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

 

[kjv1611]

Cajun,

I like the SQL there very much - it is a good bit of typing, but is shorter in space, and I would imagine is much faster. If you wouldn't mind, I would like to see your code you mentioned for speeding it up with just VB. Even if I don't use it here, I can use it as a learning experience for future projects.

If I use the SQL, would that take care of all the contitional statements? Well, I'll give it a shot in the mean time..

I really enjoy this stuff, but it can become cumbersome at times. The good thing about my comming into situations like this is that I am able to learn from my experience - both by asking questions here, and from my attempts to fix the problems. One example was I learned for Excel VBA that if you use Selection.Clear, it does not deselect an item, but actually deletes it. That was not what I wanted for the particular code, but could be useful in the future.

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]

To handle the date issue, simply add the following where clause to the update query.

WHERE [DateUpdated]=Date();

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