如何插入＆QUOT;全＆QUOT; DAO记录与VBA表 [英] How to insert "Entire" DAO recordset into a table with VBA

问题描述

我有被创造了良好的DAO记录，我可以从一组转移记录表，这是由行完成行，效果很好，但我transfering大量数据，一下子让这可能需要一很长一段时间一行一行。

有没有办法来传​​输整个记录一气呵成，而不是排排

请参阅下面的当前code使用 -

 昏暗SendE1作为DAO.Recordset

设置SendE1 = CurrentDb.OpenRecordset（SELECT TBL_ImportTable。* FROM TBL_ImportTable，dbOpenDynaset）

SendE1.MoveLast

做直到SendE1.EOF

的SQLInsert =INSERT INTO TBL_E1Jobs（起始日期，开始时间，结束日期，结束时间，地点，用户名，WorkStationID，DocumentNumber，E1Shift，OperSeq，基金，AdjustedforShifts，WEEKNUM）＆放大器; _
公共值（'与＆amp; SendE1（开始日期）及'，'与＆amp; SendE1（开始时间）及'，'与＆amp; SendE1（结束日期）及'，' ＆安培; SendE1（结束时间）及'，'与＆amp; SendE1（位置）及'，'与＆amp; SendE1（用户ID）及'，'与＆amp; SendE1（ WorkstationID）及'，'与＆amp; SendE1（DocumentNumber）及'，'与＆amp; SendE1（E1Shift）及'，'与＆amp; SendE1（OperSeq）及'，'与＆amp; SendE1（设施）及'，'与＆amp; SendE1（AdjustedforShifts）及'，'与＆amp; SendE1（WEEKNUM）及'）

DoCmd.RunSQL（的SQLInsert）

SendE1.MoveNext

循环


SendE1.Close
设置SendE1 =什么
 

解决方案

@cularis是正确的。正确的方法做，这是一个SQL查询。看了您的意见，以他的回答，有几个可以采取的步骤，以避免歼灭的数据还没有被复制：

 昏暗的分贝作为DAO.DATABASE，RECCOUNT只要

获取你的导入表后进行比较的记录总数
RECCOUNT = DCOUNT（*，TBL_ImportTable）

这一行是很重要的！在每次调用CurrentDb一次新的数据库对象返回
这将导致问题为我们以后
设置DB = CurrentDb

添加的记录，是一定要使用我们的数据库对象，而不是CurrentDb
db.ExecuteINSERT INTO TBL_E1Jobs（起始日期，开始时间，...，WEEKNUM）＆放大器; _
           选择开始日期，开始时间，...，WEEKNUM＆放大器; _
           从TBL_ImportTable，dbFailOnError

db.RecordsAffected现在包含上述被插入的记录数
因为CurrentDb返回一个新的数据库对象时，总是CurrentDb.RecordsAffected = 0
如果RECCOUNT = db.RecordsAffected然后
    db.ExecuteDELETE * FROM TBL_ImportTable，dbFailOnError
结束如果
 

请注意，如果你运行这些查询的链接的ODBC表，您将需要包括 dbSeeChanges 选项（即 dbFailOnError + dbSeeChanges ）。

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.

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

解决方案

@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:

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 dbSeeChanges option (ie, dbFailOnError + dbSeeChanges).

这篇关于如何插入＆QUOT;全＆QUOT; DAO记录与VBA表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！