两个表间数据替换为什么会很慢

wyhwbc　发表于：2011-01-17 11:03:55 　

下列程序运行为什么会很慢?

Private Sub 数据导入_Click()
Dim rs_tmp As Recordset
    Dim strSql As String
    Dim AddTimes As Integer, UpdateTimes As Integer
    Dim ImportStr As String

    Set rs_tmp = CurrentDb.OpenRecordset("select * from 表2", dbOpenDynaset, dbReadOnly)
    Set rs = CurrentDb.OpenRecordset("select * from 表1", dbOpenDynaset, dbDenyRead)

    ImportStr = ""

    AddTimes = 0
    UpdateTimes = 0

    rs_tmp.MoveFirst
    Do While Not rs_tmp.EOF
      If rs_tmp.Fields("条码号") <> "" Then
        strSql = "[条码号]=" & Chr(34) & rs_tmp.Fields("条码号") & Chr(34)
        rs.FindFirst (strSql)
        If (rs.NoMatch = False) Then '如果表1和表2条形码相同，则把表2字段[累借次数]和[累借天数]的数据更新到表1中。
          rs.Edit

          rs.Fields("累借次数") = rs_tmp.Fields("累借次数")
          rs.Fields("累借天数") = rs_tmp.Fields("累借天数")

          rs.Update
          UpdateTimes = UpdateTimes + 1
        Else '如果表1和表2条形码不相同，则把表2字段[条码号]、[累借次数]和[累借天数]的数据插入到表1中。
          rs.AddNew
          rs.Fields("条码号") = rs_tmp.Fields("条码号")
          rs.Fields("累借次数") = rs_tmp.Fields("累借次数")
          rs.Fields("累借天数") = rs_tmp.Fields("累借天数")

          rs.Update
          AddTimes = AddTimes + 1

        End If
      End If

      rs_tmp.MoveNext
    Loop

    rs_tmp.Close
    rs.Close

    MsgBox "导入结果：更新记录数[" & UpdateTimes & "] 插入记录数[" & AddTimes & "]"
End Sub

Top

煮江品茶发表于：2011-01-17 15:34:26

把记录集的范围缩小即可：

Private Sub 数据导入_Click()
    Dim rs_tmp As Recordset, rs As Recordset
    Dim strSql As String
    Dim AddTimes As Integer, UpdateTimes As Integer
    Set rs_tmp = CurrentDb.OpenRecordset("select * from 表2 where 条码号 <> ''", dbOpenDynaset, dbReadOnly)
    AddTimes = 0
    UpdateTimes = 0
    rs_tmp.MoveFirst
    Do While Not rs_tmp.EOF
        strSql = "[条码号]=" & Chr(34) & rs_tmp.Fields("条码号") & Chr(34)
        Set rs = CurrentDb.OpenRecordset("select * from 表1 Where " & strSql, dbOpenDynaset, dbDenyRead)
        If rs.EOF = False Then
          rs.Fields("累借次数") = rs_tmp.Fields("累借次数")
          rs.Fields("累借天数") = rs_tmp.Fields("累借天数")
          rs.Update
          UpdateTimes = UpdateTimes + 1
        Else
          rs.AddNew
          rs.Fields("条码号") = rs_tmp.Fields("条码号")
          rs.Fields("累借次数") = rs_tmp.Fields("累借次数")
          rs.Fields("累借天数") = rs_tmp.Fields("累借天数")
          rs.Update
          AddTimes = AddTimes + 1
        End If
        rs_tmp.MoveNext
        rs.Close
    Loop
    rs_tmp.Close
    Set rs = Nothing
    Set rs_tmp = Nothing
    MsgBox "导入结果：更新记录数[" & UpdateTimes & "] 插入记录数[" & AddTimes & "]"
End Sub

Top

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

Access交流中心

两个表间数据替换为什么会很慢