批量生成数据,优化代码,节省时间,看看能否做到?谢谢

yaw　发表于：2018-09-12 07:57:56 　

点击下载此附件

各位老师我依据下面的代码将数据进行拆分(将这个LEVE_ID_MAX值为15拆分),追加到tblTemp表中,但是出现一个问题所需要时间太长1个多小时,表"tblTemp追踪单整理"一共3000条数据,请问有没有其他VBA代码,来快速生成数据,节省时间,谢谢, 帮助修改一下附件,谢谢

Sub 拆分()
On Error Resume Next
Dim S As String
Dim rst1 As ADODB.Recordset
Dim str1 As String
Dim x As Integer
Dim VarReturn As Variant
str1 = "SELECT * FROM tblTemp追踪单整理"
Set rst1 = New ADODB.Recordset
rst1.Open str1, CurrentProject.Connection, adOpenStatic, adLockReadOnly
VarReturn = SysCmd(acSysCmdSetStatus, " 数据正在生成中...稍后...  ")
If rst1.RecordCount > 0 Then
rst1.MoveFirst
x = 0

Set rst = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)
For i = 1 To rst1.RecordCount
      For ii = 1 To rst1.Fields("LEVE_ID_MAX")
         S = ii
         DoCmd.RunSQL "INSERT INTO tblTemp SELECT '" & S & "' AS LEVE_ID, '" & rst1.Fields("END_ITEM") & "' AS END_ITEM, '" & rst1.Fields("ITEM_DESC") & "' AS ITEM_DESC"
      x = x + 1
      Next
      rst1.MoveNext
Next
End If

ReturnValue = SysCmd(acSysCmdUpdateMeter, x)
VarReturn = SysCmd(acSysCmdSetStatus, "数据已完成！ ")
MsgBox "数据更新完毕，此次操作共新增：" & x & " 条记录！", vbInformation + vbOKOnly, "Level_ID数据更新"

End Sub

Top

Natsume Takashi 发表于：2018-09-12 08:47:16

打开的记录集是tbltemp

直接记录集 .addnew 不是会快点吗？

Top

yaw 发表于：2018-09-12 10:18:05

Natsume Takashi 老师您受累给修改一下,行吗?

Top

Natsume Takashi 发表于：2018-09-12 13:44:14

  For i = 1 To rst1.RecordCount
        For ii = 1 To rst1.Fields("LEVE_ID_MAX")
            S = ii


             rst.AddNew
             rst!LEVE_ID = S
             rst!END_ITEM = rst1.Fields("END_ITEM")
             rst!ITEM_DESC = rst1.Fields("ITEM_DESC")
             rst.Update
            'DoCmd.RunSQL "INSERT INTO tblTemp SELECT '" & S & "' AS LEVE_ID, '" & rst1.Fields("END_ITEM") & "' AS END_ITEM, '" & rst1.Fields("ITEM_DESC") & "' AS ITEM_DESC"
         x = x + 1
        Next
        rst1.MoveNext
    Next
End If

Top

yaw 发表于：2018-09-12 16:14:47

谢谢老师帮助!

Top

总记录：4篇页次：1/1 9 1 :

Access交流中心

批量生成数据,优化代码,节省时间,看看能否做到?谢谢