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

@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 laterRecCount = 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 CurrentDbdb.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 = 0If RecCount = db.RecordsAffected Then    db.Execute "DELETE * FROM TBL_ImportTable", dbFailOnErrorEnd If

Please note that if you run those queries on linked ODBC tables, you will need to include the dbSeeChanges option (ie, dbFailOnError + dbSeeChanges).

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