Handle "big" Recordset from Access in VBA, DoEvents

Question

My goal is to calculate the phone cost for every call coming from our call-manager. The script works on small tables (<1k), but get stucked at the actual data.

My call table has 160k rows. The q_get_called result 6 columns. The script works fine everytime till row 7431. I tried on smaller tables and it did his Job also. If I don't use DoEvents function Access won't respond anymore but Script also run only until around row 8000 after 30 minutes.

General Question: Is there something important in vba, like Setting variables back to 0 after each Loop, to handle such data sizes?

I already reduced the query to those 4 columns needed. Might be the Select Case Statement better? Or is there a better way to update the cost into the call_table, than my insertSQL-approach?

If I pause the script after several minutes, it got stuck at the DoEvents() function.

Set Datenbank = CurrentDb
Set rs_calls = Datenbank.OpenRecordset("q_get_called", dbOpenDynaset)
    'Loop through all call-data to calculate cost
    Do While Not rs_calls.EOF
    callID = rs_calls!globalCallID_CallId
    internal = rs_calls!IfInternal
    inside = rs_calls!FromInside
    If inside = 0 Or internal = -1 Then 'cost is Zero if call coming from outside or is internal
        cost = 0
    Else 'else proceed calculation
        callingNr = rs_calls!callingPartyNumber
        calledNr = rs_calls!finalCalledPartyNumber
        duration = rs_calls!duration

        If Left(calledNr, 3) Like "0??" Then 'domestic
            multiplier = 1
        ElseIf Left(calledNr, 3) Like "00?" Then multiplier = 5 'national
        ElseIf Left(calledNr, 3) Like "000" Then multiplier = 10 'international
        Else: multiplier = 0
        End If
        'Select Case Left(calledNr, 3) 'Maybe Select case is smarter?
        '    Case "0??"
        '        multiplier = 1
        '    Case "00?"
        '        multiplier = 5
        '    Case "000"
        '        multiplier = 10
        '    Case Else
        '        multiplier = 0
        'End Select
        cost = (duration / 60) * multiplier  
    End If
    insertSQL = "UPDATE tbl_cdr SET cost = " & cost & " WHERE globalCallID_callID = " & callID
    Datenbank.Execute (insertSQL) 'set cost column
    rs_calls.MoveNext
    subForm = DoEvents() 'pass control to OS
Loop

Answer 1

So, as solution doing everything directly in access with an update-query works fine for now.

UPDATE tbl_cdr SET tbl_cdr.cost = IIf(Left([tbl_cdr].[calledNumber],3)="000",
([tbl_cdr].[duration]/60)*10, IIf(Left([tbl_cdr].[calledNumber],2)="00",
([tbl_cdr].[duration]/60)*5, IIf (Left([tbl_cdr].[calledNumber],1)="0",
[tbl_cdr].[duration]/60,0)));

That worked out in <1min. My calculation will get more complicated though. Like seperating the calls also by the callingNumber, and use different Multiplier constants according to them.

Edit: I implemented a little bit more logic now and struggling to do everything in one SQL Update Statement

Is it possible to enter subqueries to the update Statement like this:

UPDATE tbl_cdr SET [tbl_cdr].[cost]=
 IIf([tbl_cdr].[fromInside]=-1,
  IIf(Left([tbl_cdr].[fncpn],3)="000",
   ([tbl_cdr].[duration]/60)*
   (SELECT international FROM [tbl_cc] WHERE ccc=Left(cpn,4);),
   IIf(Left([tbl_cdr].[fncpn],2)="00",
    ([tbl_cdr].[duration]/60)*
    (SELECT national FROM [tbl_cost] WHERE ccc=Left(cpn,4);),
     IIf(Left([tbl_cdr].[fncpn],1)="0",
     ([tbl_cdr].[duration]/60)*
     (SELECT domestic FROM [tbl_cost] WHERE ccc=Left(cpn,4);),
     0)
   )
  )
 ,0)
;`

ErrorLog: "Operation must use an updateable query"

-I do have all permissions on the Folder

-the linked tables contain Primary keys

Or is it just not the right way to mix this IIf-clauses with SQL?

As solution might work to have a query for each different callingCountry

Like this:

UPDATE tbl_cdr AS cdr SET cdr.cost = cdr.duration* (SELECT cc.international FROM tbl_costConstant AS cc WHERE cc.callingCountryCode = 1760) WHERE cdr.finalCalledPartyNumber Like "000%" AND cdr.callingPartyNumber Like "1760%";

This seems like a big workaround though.