access vba程序循环处理10万级别数据慢的问题

[gxlubinrobin2]

目前我有个vba的程序，按了按钮后会根据输入数据源（也就是第一张图片里的这些excel），生成我想要的excel文件，在按钮事件里会调用一个AllocateCommMaterial方法，现在的问题是点了按钮，我电脑啥都不干的情况下需要运行1小时50分左右，主要的时间都花在这个AllocateCommMaterial方法，这个方法大约需要1小时30多分钟。
现在我就是想优化这块的速度，请问大神有好的建议吗？

备注：刚开始我不知道是这个AllocateCommMaterial方法慢，以后是使用了access数据库的原因导致的慢，所以想是不是换成其他数据库，用其他语言就会提升速度，但是再看了这个方法里面的逻辑以后，我认为就算改成其他数据库，其他语言，如果按照现在的这种处理流程，应该也是白搭,b不知道我这样理解对不对？（因为这个方法里要循环10万左右的数据，每个循环里面还要更新另外2个表的对应id相同的记录）




AllocateCommMaterial方法

Function AllocateCommMaterial(Threshold As Double)

Dim rstQry As Recordset
Dim Part As String
Dim WO As String
Dim WOShortage As Long
Dim ETA As Long
Dim i As Integer
Dim Wi As Long

PartFlag = 0
WOFlag = 0

Set rstQry = CurrentDb.OpenRecordset("0403_Comm_WO_Parts_Status")

rstQry.MoveLast
rstQry.MoveFirst

WOPre = "0000"

DoCmd.SetWarnings (False)

While Not rstQry.EOF

WO = rstQry![WO No]
WOShortage = rstQry![WO Demand Qty]
Part = rstQry![Part No]

'pg.Run Count, RowCount, "正在运行:" & Part

PartOHNet = IIf(IsNull(rstQry![OH Net]), 0, rstQry![OH Net])
PartOHAllocated = IIf(IsNull(rstQry![OH Allocated Qty]), 0, rstQry![OH Allocated Qty])

PartReceNet = IIf(IsNull(rstQry![Receiving Net]), 0, rstQry![Receiving Net])
PartReceAllocated = IIf(IsNull(rstQry![Receiving Allocated Qty]), 0, rstQry![Receiving Allocated Qty])

PartTransitNet = IIf(IsNull(rstQry![Transit Net]), 0, rstQry![Transit Net])
PartTransitAllocated = IIf(IsNull(rstQry![Transit Allocated Qty]), 0, rstQry![Transit Allocated Qty])

PartWONet = IIf(IsNull(rstQry![WO Net]), 0, rstQry![WO Net])
PartWOAllocated = IIf(IsNull(rstQry![WO Allocated Qty]), 0, rstQry![WO Allocated Qty])

PartOSPNet = IIf(IsNull(rstQry![OSP Net]), 0, rstQry![OSP Net])
PartOSPAllocated = IIf(IsNull(rstQry![OSP Allocated Qty]), 0, rstQry![OSP Allocated Qty])

    If WOShortage <= PartOHNet Then    'No shortage
         
         WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No Shortage', [OH Allocate] = " & WOShortage & _
                    " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                  
         PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Allocated Qty] = " & PartOHAllocated + WOShortage & _
                ",[OH Net] =  " & PartOHNet - WOShortage & " WHERE [Part No] = '" & Part & "'"
                
    ElseIf WOShortage <= PartOHNet + PartWONet Then      'No shortage - WO
        
        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No shortage - WO', [OH Allocate] = " & PartOHNet & _
                    ",[WO Allocate]= " & WOShortage - PartOHNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
                    ",[WO Net] = " & PartWONet - WOShortage + PartOHNet & _
                    ",[WO Allocated Qty] = " & WOShortage - PartOHNet + PartWOAllocated & _
                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
                    
    ElseIf WOShortage <= PartOHNet + PartWONet + PartOSPNet Then      'OSP'
        
        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'OSP', [OH Allocate] = " & PartOHNet & _
                    ",[WO Allocate] = " & PartWONet & _
                    ",[OSP Allocate]= " & WOShortage - PartOHNet - PartWONet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
                    ",[WO Net] = 0" & _
                    ",[OSP Net] = " & PartOSPNet - WOShortage + PartOHNet + PartWONet & _
                    ",[OSP Allocated Qty] = " & WOShortage - PartWONet - PartOHNet + PartOSPAllocated & _
                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
    
    ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet Then      'Receiving
        
        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Receiving', [OH Allocate] = " & PartOHNet & _
                    ",[WO Allocate] = " & PartWONet & _
                    ",[OSP Allocate] = " & PartOSPNet & _
                    ",[Receiving Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
                    ",[WO Net] = 0" & _
                    ",[OSP Net] = 0" & _
                    ",[Receiving Net] = " & PartReceNet - WOShortage + PartOHNet + PartWONet + PartOSPNet & _
                    ",[Receiving Allocated Qty] = " & WOShortage - PartOHNet - PartWONet - PartOSPNet + PartReceAllocated & _
                    ",[OSP Allocated Qty]= " & PartWOAllocated + PartOSPNet & _
                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
                    
    ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet + PartTransitNet Then      'Transit
        
        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Transit', [OH Allocate] = " & PartOHNet & _
                    ",[WO Allocate] = " & PartWONet & _
                    ",[OSP Allocate] = " & PartOSPNet & _
                    ",[Receiving Allocate] = " & PartReceNet & _
                    ",[Transit Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet - PartReceNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
                    ",[WO Net] = 0" & _
                    ",[OSP Net] = 0" & _
                    ",[Receiving Net] = 0" & _
                    ",[Transit Net] = " & PartTransitNet - WOShortage + PartReceNet + PartOSPNet + PartWONet + PartOHNet & _
                    ",[Transit Allocated Qty] = " & WOShortage - PartReceNet - PartOHNet - PartWONet - PartOSPNet + PartTransitAllocated & _
                    ",[Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & _
                    ",[OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & _
                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
                    

    Else   'if Parts are Shortage =      WOShortage > PartOHNet + PartReceNet , then update W1, W2, W3......
        
        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [OH Allocate] = " & PartOHNet & ", [WO Allocate] = " & PartWONet & ", [Receiving Allocate]= " & PartReceNet & ", [Transit Allocate]= " & PartTransitNet & ", [OSP Allocate]= " & PartOSPNet
        
        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0, [WO Net] = 0, [Receiving Net] = 0, [Transit Net] = 0, [OSP Net] = 0, [OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & ", [Transit Allocated Qty]= " & PartTransitAllocated + PartTransitNet & ", [Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & ", [WO Allocated Qty] = " & PartWONet + PartWOAllocated & ", [OH Allocated Qty] = " & PartOHNet + PartOHAllocated
        
        ETA = PartOHNet + PartWONet + PartReceNet + PartTransitNet + PartOSPNet
        
        i = 1
        While i <= 54
            
            Wi = IIf(IsNull(rstQry.Fields("W" & i & " Net")), 0, rstQry.Fields("W" & i & " Net"))
            
            If Wi > 0 Then
                
                ETA = ETA + Wi
                
                If ETA < WOShortage Then    'ETA not enough for WOShortage
                    
                    WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi
                    PartSQL = PartSQL & ",[W" & i & " Net] = 0"
                
                Else   'ETA is enough for WOShortage, end the loop
                    
                    If i < 10 Then
                        
                            WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W0" & i & "'" & _
                            " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
                        Else
                            WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W" & i & "'" & _
                            " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
                    
                    End If
                    
                        PartSQL = PartSQL & ",[W" & i & " Net] = " & ETA - WOShortage & _
                        " WHERE [Part No] = '" & Part & "'"
                    
                        GoTo LabelA
                
                End If
            
            End If
            
            i = i + 1
        
        Wend

LabelA:
        
        If i = 55 Then
            
            WOSQL2 = WOSQL2 & ",[Result] = 'W55...'" & _
                     " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
            
            PartSQL = PartSQL & " WHERE [Part No] = '" & Part & "'"
        
        End If

    End If
    
    
    DoCmd.RunSQL WOSQL2
    DoCmd.RunSQL PartSQL
    
WOPre = WO
rstQry.MoveNext


Wend       'Allocate material


End Function

AllocateCommMaterial方法的说明：

[gxlubinrobin2]

不需要仔细看代码，逻辑也不复杂，我写清楚了，这个方法就是要循环处理10万条数据，然后每个循环里都要更新2个表对应的记录，所以慢，是加起来时间慢，不是里面某几句慢

[gxlubinrobin2]

我现在能想到的是把这个方法用c#去实现，因为access vba里不支持多线程，在C#里面用多线程去循环处理，然后vba调用c#的dll，不知道是不是可行

[ACMAIN_CHM]

代码太长了，没耐心看了。建议楼主可以先加一些时间输出，看一下具体是哪一句哪，然后再进行分析讨论。