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

2018-05-25 11:04:54 2.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›
gxlubinrobin2 gxlubinrobin2 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网 我也要 + 加入社区
Access基础教程--第八章 使用VBA编程  本章内容 ◆ VBA编程基础◆ 创建和设计VBA程序◆ VBA程序调试与运行  一、VBA编程基础知识     在执行简单的操作时，宏可以完成，如窗体的打开与关闭、工具栏的显示和隐藏等。对于稍复杂一些的任务，则可用Visual Basic for Application进行编程完成。 1．VBA的数据类型 下面对VBA中的数据类型、数据类型 阅读详情
目前我有个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方法的说明：











...全文
Excel中使用VBA访问Access数据库 VBA访问Access数据库 1. 通用自动化语言VBA VBA(Visual Basic For Application）是一种通用自动化语言，它可以使Excel中的常用操作自动化，还可以创建自定义的解决方案。 Excel中使用VBA的优点： 使固定、重复的任务程序自动化，提高工作效率。 可进行复杂的数据处理和分析。 可自定义Excel函数、工具栏、菜单和界面。 可连接多种数据... 阅读详情

相关推荐

vba连接访问数据库（access）

在“vba连接访问数据库（access）”这个主题中，我们将探讨如何通过VBA代码与Access数据库进行交互，这对于数据处理、报告生成以及自动化工作流程非常有用。

chenhua20000

Access VBA程序设计全书

本文还有配套的精品资源，点击获取 简介：Access VBA是Microsoft Access内置的编程语言，用于自定义和自动化数据库操作。本大全集包含了一系列VBA代码示例、函数说明和技巧，旨在提升用户对Access数据库编程的能力。内容涉及VBA函数、对象模型、控制结构、事件驱动编程、模块化编程和调试优化等关键知识点。掌握这些内容能够帮助用户在Access环境中开发高效...

Omoo

全面掌握Access-VBA函数

本文还有配套的精品资源，点击获取 简介：在Access数据库开发中，VBA作为核心编程语言，提供了丰富的函数库，支持数据处理、流程控制、数学运算、字符串操作、日期时间处理以及错误处理和调试等。本大全详细介绍了Access-VBA中常用函数的使用方法，参数和返回值，旨在帮助开发者通过掌握这些函数，构建高效、灵活的应用程序，实现数据管理和自动化任务。 1. Acces...

郁林成森

Excel VBA与数据库(Access)整合笔记

通过熟练掌握这两者的结合应用，能够解决各种数据管理问题，实现数据的高效利用和自动化处理。

flylzy

Access Vba 编程教程

数据导出：编写程序将Access数据导出为其他格式，如Excel、CSV或文本文件。5. 进程自动化：批量处理大量数据，如批量更新记录、生成报表等。

stram10
评论 3
收藏 已收藏
分享
返回顶部
gxlubinrobin2
gxlubinrobin2
Access 7,731 成员 28,797 内容
+ 加入社区