使用 VBA 从一个访问表写入另一个访问表，而不是 SQL

Question

在 Access 中，我正在尝试编写一些代码，这些代码将简单地将所有行从一个表复制到另一个表中。

我尽量不使用 SQl，这是希望让 VB 这样做会快得多，目前用于完成这项工作的查询需要一个小时。

它在第一个记录"记录被删除"处中断。尽管从中导出记录的表中有记录。

我想知道是否有人对如何解决这个问题有任何想法？

或者如果我以错误的方式做到这一点？

Public Sub ClientsImport()
Dim rs As Recordset, rsIN As Recordset
Dim db As Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblDIFCClients_Import2", dbOpenDynaset)
Set rsIN = db.OpenRecordset("tblDIFCClients_import_IN", dbOpenDynaset)
Logit "Start:tblDIFCClients_import..."
DoCmd.RunSQL ("delete * from tblDIFCClients_import_IN")
Logit "... Cleared holding table..."
CurrentDb.Execute ("qryMatch_TblClients1")
Logit "... Grabbed from Server..."
Logit "... Updating Live table..."
DoCmd.RunSQL ("delete * from tblDIFCClients_import2")
Logit "Append Clients"
rsIN.MoveFirst
With rs
    Do Until rsIN.EOF
    .AddNew
    ![fldDIFCClientsRAWID] = rsIN![fldDIFCClientsRAWID]
    ![fldClientID] = rsIN![fldClientID]
    ![fldClientname] = rsIN![fldClientname]
    ![fldStyleSheet] = rsIN![fldStyleSheet]
    ![fldcustomjs] = rsIN![fldcustomjs]
    ![fldBannerImage] = rsIN![fldBannerImage]
    ![fldLogoImage] = rsIN![fldLogoImage]
    ![fldVanityURL] = rsIN![fldVanityURL]
    ![fldDescription] = rsIN![fldDescription]
    ![fldBusinessNumber] = rsIN![fldBusinessNumber]
    ![fldBasePrimaryColour] = rsIN![fldBasePrimaryColour]
    ![fldDefaultForwardingEmail] = rsIN![fldDefaultForwardingEmail]
    ![fldHasPFPs] = rsIN![fldHasPFPs]
    ![fldHasMicrosite] = rsIN![fldHasMicrosite]
    ![fldHasEvents] = rsIN![fldHasEvents]
    ![fldDateTimeStamp] = rsIN![fldDateTimeStamp]
    ![fldModified] = rsIN![fldModified]
    ![fldDownloaded] = rsIN![fldDownloaded]
    ![fldIgnore] = rsIN![fldIgnore]
    .Update
    rsIN.MoveNext
End With
rs.Close
Set rs = Nothing
rsIN.Close
Set rsIN = Nothing
Logit "End: Clients Import"
Logit ""
Debug.Print "Done:Clients"

Answer 1

首先，我真的不认为循环访问记录集会比通过SQL复制表更快。

实际上，您的代码在读取之前正在删除要从中复制的表的内容，因此row is deleted错误。 您从tblDIFCClients_import_IN获得记录集：

Set rsIN = db.OpenRecordset("tblDIFCClients_import_IN", dbOpenDynaset)

删除其内容：

DoCmd.RunSQL ("delete * from tblDIFCClients_import_IN")

然后尝试从中读取：

Do Until rsIN.EOF

您正在执行介于两者之间的qryMatch_TblClients1。 这是否填充tblDIFCClients_import_IN？ 如果是这样，这就是您希望加载到tblDIFCClients_Import2中的内容，那么简单地移动Set rsIN = db.OpenRecordset...行以在调用执行qryMatch_TblClients1之后发生应该可以修复它。