如何使用 VBA 将“整个”DAO 记录集插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6843017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert "Entire" DAO recordset into a table with VBA
提问by Steven Whelan
I have a DAO recordset that gets created fine and I can transfer the records from the set to a table, this is done row by row and works well but I am transfering a large amount of data at once so this can take a very long time row by row.
我有一个创建良好的 DAO 记录集，我可以将记录从集合传输到表，这是逐行完成的并且效果很好，但是我正在一次传输大量数据，因此这可能需要很长时间一行一行。
Is there a way to transfer the ENTIRE recordset in one go, rather than row by row
有没有办法一次性传输整个记录集，而不是逐行传输
See below for current code in use -
有关当前使用的代码，请参见下文 -
Dim SendE1 As DAO.Recordset
Set SendE1 = CurrentDb.OpenRecordset("SELECT TBL_ImportTable.* FROM TBL_ImportTable", dbOpenDynaset)
SendE1.MoveLast
Do Until SendE1.EOF
sqlinsert = "INSERT INTO TBL_E1Jobs (StartDate, StartTime, EndDate, EndTime, Location, UserID, WorkStationID, DocumentNumber, E1Shift, OperSeq, Facility, AdjustedforShifts, WeekNum)" & _
" VALUES ('" & SendE1("StartDate") & "', '" & SendE1("StartTime") & "', '" & SendE1("EndDate") & "', '" & SendE1("EndTime") & "', '" & SendE1("Location") & "', '" & SendE1("UserID") & "', '" & SendE1("WorkstationID") & "', '" & SendE1("DocumentNumber") & "', '" & SendE1("E1Shift") & "', '" & SendE1("OperSeq") & "', '" & SendE1("Facility") & "', '" & SendE1("AdjustedforShifts") & "', '" & SendE1("WeekNum") & "') "
DoCmd.RunSQL (sqlinsert)
SendE1.MoveNext
Loop
SendE1.Close
Set SendE1 = Nothing
回答by mwolfe02
@cularis is correct. The right way to do this is in a SQL query. Having read your comments to his answer, there are a few steps you can take to avoid wiping out data that has not been copied:
@cularis 是正确的。执行此操作的正确方法是在 SQL 查询中。阅读您对他的回答的评论后，您可以采取一些步骤来避免清除尚未复制的数据：
Dim db As DAO.Database, RecCount As Long
'Get the total number of records in your import table to compare later
RecCount = DCount("*", "TBL_ImportTable")
'This line is IMPORTANT! each time you call CurrentDb a new db object is returned
' that would cause problems for us later
Set db = CurrentDb
'Add the records, being sure to use our db object, not CurrentDb
db.Execute "INSERT INTO TBL_E1Jobs (StartDate, StartTime, ..., WeekNum) " & _
"SELECT StartDate, StartTime, ..., WeekNum " & _
"FROM TBL_ImportTable", dbFailOnError
'db.RecordsAffected now contains the number of records that were inserted above
' since CurrentDb returns a new db object, CurrentDb.RecordsAffected always = 0
If RecCount = db.RecordsAffected Then
db.Execute "DELETE * FROM TBL_ImportTable", dbFailOnError
End If
Please note that if you run those queries on linked ODBC tables, you will need to include the dbSeeChangesoption (ie, dbFailOnError + dbSeeChanges).
请注意，如果您在链接的 ODBC 表上运行这些查询，则需要包含dbSeeChanges选项（即dbFailOnError + dbSeeChanges）。
回答by Jacob
Not a DAO, but a SQL solution, that does what you need:
不是 DAO，而是 SQL 解决方案，可以满足您的需求：
INSERT INTO TBL_E1Jobs (StartDate, StartTime, EndDate ...)
SELECT StartDate, StartTime, EndDate ... FROM TBL_ImportTable
