How to insert "Entire" DAO recordset into a table with VBA

Question

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

Subcelestial

Answer 1

@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).

Dynamiter

Would a transaction ensure db.Execute INSERT completes before moving on? I think it might, but I'm not sure. – Roach

I don't know. I've never personally experienced the problem the OP seems to be having where the code moved on before the query was complete. But maybe I've never really tested that out on a slow network where the query being completely finished was so critical. – Dynamiter

Answer 2

Not a DAO, but a SQL solution, that does what you need:

INSERT INTO TBL_E1Jobs  (StartDate, StartTime, EndDate ...) 
SELECT StartDate, StartTime, EndDate ... FROM TBL_ImportTable

INSERT INTO ... SELECT MSDN

Dismissive

I did originally have that as the solution however I could not find a method to ensure that the SQL solution would complete before starting the next SQL section of code. – Subcelestial

DoCmd.RunSQL should only return when the SQL command is finished/aborted with an error. – Dismissive

What I had was an insert sql command, then immediately afterwards a delete sql command. So insert would do the data transfer from IMPORT to E1JOBS. Then the delete would wipe the import table...I was noticing though that if the Insert function took a long time (the back end is on a networked server not local desktop, so network congestion plays its part) then the delete sql would kick in before it finished and delete the table. Leaving me with half the data not transfered. – Subcelestial

Wow, that shouldn't happen. I can't think of something causing this, maybe DWF knows ;), but you could put in some kind of check after INSERT ... SELECT if all rows are in the new table. – Dismissive

Agreed it shouldnt happen, its had me confused for a while. Thats why I went for a DAO but the only method of transfer I know is line by line. Which removes the risk of the lost data but takes forever. A check could be something to look at.....I'll have a think about that – Subcelestial

You should never use DoCmd.RunSQL -- always use a DAO database object and .Execute with the dbFailOnError switch and an error handler. If you want a simple replacement for DoCmd.RunSQL that does that, search SO for my SQLRun() function. – Jerricajerrie