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

 

[CajunCenturion]

Okay, here is the loop, with the Date taken into account.

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 DateUpdated = #" & Date & "#;")

With rs
   .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 With
Set rs = Nothing
MsgBox lngAccount & " Accounts were updated."

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

 

[kjv1611]

Thanks... I'll look at it more, probably tomorrow...

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,
I have just gotten to look again at this code today (4:45pm EDT), and was wondering what these items in your code were: (I noticed them in your earlier post, but haven't the slightest what they mean or stand for). I thought I'd try the VBA help "answer wizard", but no find. Here are the items I'd like some info on (either a reference to a web site or short explaination (if you don't mind)):

1.)abs
2.)sgn
3.)Nz

It may just be my inexperrience showing, but I do not recognize them from anything I am aware of (to date). Any info/reference(s) would be greatly appreciated.

Thanks,

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

 

[RoyVidar]

It might take some time to master the usage of the help files, but it does pay off!

If they don't show up using the answer wizard (which they do on my setup), then try typing the different keywords in VBE and hit F1 while the cursor is within one of them.

Searching these fora with keywords NZ or Abs should give numerous hits, Sgn is probably not so much referenced.

Roy-Vidar

 

[CajunCenturion]

Well they are intrinsic functions to VBA.


The Nz function looks at the first parameter, and if it is not null, then it returns the value of the first parameter. If it is null, then it returns the second parameter.
In other words ==>
Nz(rs(I), 0) ==> IIF((IsNull(rs(I))), 0, rs(I))
You may not actually need this function since your field types are currency, and I'm not sure, but I don't think currency fields can be null.
Without the Nz function, that line would read as follows:

intCount = intCount + Abs(Sgn(rs(I)))

The Sgn (Sign) function returns a 1 if the number is positive, a 0 if it's 0, and a -1 if the number is negative.

The Abs (Absolute Value) returns the Absolute Value of the number. In your case, it turns the -1 into a +1.

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

 

[kjv1611]

Hmmm.. I want to try different things with that, then. Thanks for the info, that will be most valuable for future projects, I'm sure. Well, I tried the F1 key instead of typing in the word in the answer wizard, and it came right up. I've used that before, but just didn't consider it for these - DOH! Anyways, I tried the code mentioned above for the first time this morning (8:15am) and got an error on the following line of code:

Set rs = db.OpenRecordset("SELECT * FROM tblHIstoricalbyBatch WHERE DateUpdated = #" & Date & "#;")

This is the error message I received:
[BLUE]
Run-time error '3061';
Too fwe parameters. Expected 1.[/BLUE]

I would imagine it is something to do with the section around 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

 

[kjv1611]

I tried just taking out the 2 inner most quotes, but that was no good.. probably just something I'm not aware of yet.. Any ideas there?

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]

Okay, I fixed it -the field name is actually UpdateDate, not DateUpdated - I believe that has fixed it, will need to set something equal to today's date in there, though, to test it more accurately. [SMILE]

thanks again for the help.

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]

It would be interesting if you could compare the time to update 61K records on your machine usng the nested IF statements, vs using this function combination. Of course, to get good results, you have to make that nothing else is running on the machine that could skew the results.

Side Note: I remember someone saying that a loop (For x = a to b :...: Next x) was slower than simply using next (For x = a to b :...: Next) and he tested it and the Next x was 3 seconds slower on a 10 million iteration loop. What he failed to take into account was that during the "next x" phase of the test, his machine automatically downloaded the lastest virus definition files, therefore the "next x" was clearly slower.

Back to the problem at hand. What is the database of the DateUpdated field in tblHistoricalbyBatch table?

What type of field is

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

 

[kjv1611]

Well, I thought I had it fixed, but now getting this error:
[BLUE]
Run-time error '3201':
No current record.[/BLUE]
at the following line:

.MoveFirst

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]

Well, you need to check that you records that meet the criteria. You can't move to the first record if you don't have any records. I don't remembers the details of DAO -- haven't used it in years -- but you need to check that you have at least one record (In ADO, EOF is set to true for empty results set) before you movefirst.


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

 

[Craig0201]

There's not need to MoveFirst. You'll be there if there is records.

Craig

PS What was wrong with Cajun's SQL solution?

 

[kjv1611]

Well, it seems that is not finding any records at all. I tried an idea I found in another post:

If rs.BOF And rs.EOF Then
     MsgBox "No records in this file"
Else
     ~code for updating the check count~
End If

And I get the message every time, "No records in this file."
- the message I coded to say if it was finding nothing. That makes no sense, b/c there are over 60000 records in the table.

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 added a fake record today so it would have something to pull with today's date in the UpdateDate field.

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]

For some reason, I think it has to do with the Date context. For example, I built a query, and inserted #05-28-04# in the criteria field under the UpdateDate field, and it worked fine. When I used the field, Date, it gave me a type mismatch 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 again ask the question: What is the datatype and format of the UpdateDate field in the tblHistoricalbyBatch table?

One possibility that you may be up against different date formats and perhaps a time component being in the database. But this is based on the assumption that UpdateDate is a Date/Time field in the table.

"SELECT * FROM tblHistoricalbyBatch WHERE UpdateDate = #" & Format(Date, "mm-dd-yy") & "#;"

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

 

[kjv1611]

Datatype is Date/Time, and I did not set a format, but set the default value to =Date(), so that when a new record is added, the date when added is put in that location. The dates showing up there look like this:
5/28/2004

I wouldn't think that was the problem, since using Date in VBA, and =Date() in the table. Are they actually two different values altogether, and that would explain it?

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... I first put a variable set to Date in the SQL string, that did the same, so took that out, and put Date back in and the only thing I changed was putting the SQL code on 2 lines instead of one with the proper & _ to combine the two lines.. and it worked!! great!! Here is the final code:

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

Thanks again for the help, Cajun.. I'm going to change the dates all to today, or either just change the code temporarily to use the most used date in the table, and see if is much faster - will post back to verify success or failure on that part.


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]

Um, okay.. I just tried the code with the most common date, "#5/24/2004#", and got this error message:
[BLUE]
Run-time error '3052':
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.[/BLUE]

Any ideas on this one?

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'm glad that you got it worked out. One suggestion, since speed is of high concern, is that also try the query without using the Nz function, because, I'm not sure that you can ever have a Null in a currency field. That will speed it up slightly, but if you get an "Invalid use of Null" error, then put the Nz function back in.

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