Yikes, this is taking an age...

[wh00t]

I have been working on a database and have set a loop to calculate a value based on imported information. The problem is, when testing with basic information, the process took 2 minutes to run through the 1100 records and do the calculation, I now have some more data to 'play' with and it consists of 24000 records (which is what it will be live), I set the loop off, and after 2 1/2 hours, I ended the program, only 15000 of the records had completed. Is there a way that I can get this to run faster? or is this the best Access can do?
code below

Dim Rs As Recordset
Dim db As Database
Dim CurDate As Date
Dim Actual As Double
Dim ProdIn As Double
Dim ProdOut As Double
Dim Bakery As String
Dim ItemGrp As Integer
Dim CurID As Integer
Dim PreCum As Double

Set db = CurrentDb
Set Rs = db.OpenRecordset("TotalsResults")

Rs.MoveFirst

Do While Not Rs.EOF

If Rs![Updated] = False Then
    PreCum = 0
    CurDate = Rs![FromDate]
    Bakery = Rs![Bakery]
    ItemGrp = Rs![ItemGrpID]
    CurID = Rs![ID]
    ProdIn = Rs![DFProductionIn]
    ProdOut = Rs![DFProductionOut]

    Do Until Rs.BOF
    On Error Resume Next
        Rs.MovePrevious
        If Rs![Bakery] = Bakery And Rs![ItemGrpID] = ItemGrp Then
            PreCum = Rs![CumulativeDFPlan]
            Exit Do
        End If
    Loop
    
    Rs.MoveFirst
    Do Until Rs![ID] = CurID
        Rs.MoveNext
    Loop

    If ProdIn = 0 And ProdOut = 0 Then
        Rs.Edit
        Rs![CumulativeDFPlan] = PreCum - Rs![DFActuals]
        Rs![Updated] = True
        Rs.Update
    End If

    If ProdIn = 0 And ProdOut <> 0 Then
        Rs.Edit
        Rs![CumulativeDFPlan] = (PreCum - Rs![DFActuals]) - ProdOut
        Rs![Updated] = True
        Rs.Update
    End If

    If ProdIn <> 0 And ProdOut = 0 Then
        Rs.Edit
        Rs![CumulativeDFPlan] = (PreCum - Rs![DFActuals]) + ProdIn
        Rs![Updated] = True
        Rs.Update
    End If

    If ProdIn <> 0 And ProdOut <> 0 Then
        Rs.Edit
        Rs![CumulativeDFPlan] = PreCum + ((ProdIn - ProdOut) - Rs![DFActuals])
        Rs![Updated] = True
        Rs.Update
    End If
End If

Rs.MoveNext

Loop

Rs.MoveFirst

Do While Not Rs.EOF
    Rs.Edit
    Rs![Updated] = False
    Rs.Update
    Rs.MoveNext
Loop

 

[GJT]

Not too sure what you are trying to do with your coding here but as a starter ...........

1. Replace this:

Do While Not Rs.EOF
Rs.Edit
Rs![Updated] = False
Rs.Update
Rs.MoveNext
Loop

with :

Currentdb.Execute ("UPDATE TotalsResults SET Updated=False WHERE Updated=True")

NB. Doesnt this now need to be set to TRUE???????

2. The sheer size of the recordset could be hogging too much memory to allow all your other calculations to be carried out easily... so I would suggest you use SQL to reduce the size of the recordset - if you can i.e.

Set Rs = db.OpenRecordset("SELECT * FROM TotalsResults WHERE Updated=False AND ...........")

3. There must be a more precise way of doing this, the fact that you are moving the recordset pointer around all over the place during the routine cant be helping matters!!!

Let me know if these pointers help......

 

[wh00t]

the updated is a Y/N flag just for the code to identify that that record has been done, and is reset when the code is finished ready for the next set of code.

the code is basically for a calculation - information for a current week + information from a previous week for the same product from the same location, so the code does
a) at each non updated record take basic information
b) find previous record with above matches and take more information
c) go back to original record and perform a calculation based on what information has been captured from the 2 records.

wash, rinse and repeat

 

[GJT]

Is there any chance that you could break this down into smaller chunks i.e.

Use a macro containing calls to varous SQL queries. These queries would generate temporary table(s) of calculated results that you use in the final SQL query to update the original source table?

Calling SQL would be much faster than moving the pointer backwards and forwards and the impact on memory would be far less intensive......(in my opinion)

 

[wh00t]

I'll give that a try, might take a while as I've done only very basic SQL queries before. Thanks for the tips

 

[VBAhole22]

Is one of your variables really named PreCum?

 

[wh00t]

there's always one

it means Previous Cumulative

 

[VBAhole22]

Couldn't resist

 

[wh00t]

Thanks Pat, the process now takes 30 mins for 24000 records.
The reason it's stored is because this database will not be for any users, it simply generates a table which is then exported to another machine which is incapable of doing this calculation (it was originally intended to, but they no longer plan to pay for the upgrade to the software on this machine)