在VBA中处理来自Access的“大”Recordset，DoEvents

Question

我的目标是计算来自呼叫管理器的每个呼叫的电话费用。该脚本适用于小型桌面（< 1k），但会受到实际数据的困扰。

我的通话表有160k行。 q_get_called结果6列。该剧本每次都能正常工作，直到7431行。我尝试了更小的桌子，并且他也完成了他的工作。如果我不使用DoEvents功能，Access将不再响应，但脚本也仅在30分钟后才运行，直到行8000左右。

一般问题：在vba中是否有重要的东西，比如在每个Loop之后将变量设置为0以处理这样的数据大小？

我已经将查询减少到需要的那4列。可能更好地选择案例陈述？还是有更好的方法来将成本更新到call_table中，而不是我的insertSQL方法？

如果我在几分钟后暂停脚本，它会卡住DoEvents（）函数。

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

因此，作为解决方案，使用update-query直接访问所有内容现在都能正常工作。

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

这在< 1min。 虽然我的计算会变得更加复杂。像分开callingNumber的呼叫一样，并根据它们使用不同的乘数常数。

编辑： 我实现了多一点点逻辑现在挣扎在一个SQL UPDATE语句做的一切

是否有可能进入子查询到这样的更新语句：

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：“操作必须使用可更新的查询”

- 我对文件夹拥有所有权限

-the链接表包含主键

或者只是不混这个IIf-clauses与SQL的正确方法？

作为解决方案可能工作有一个查询为每个不同的callingCountry

像这样：

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%";

这似乎是一个很大的解决办法，但。