Moving records from one table to another

[donavan01]

I need a sql statement that will allow me to move data from one table to another (not copy but move) currently I am using the following code

 Dim db As Database
 Set db = CurrentDb
 db.Execute "Insert INTO archive SELECT * FROM Main"
 db.Execute "DELETE FROM [Main]"

However it seems to have some issues because it wants to delete the records before everthing is moved. I have another option which is:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryArchive"
DoCmd.SetWarnings True

with a query of:

Insert INTO archive SELECT * FROM Main
DELETE FROM [Main]

But I dont know if this will guarentee a correct transfer of the data either.


Any Suggestions?

[pbaldy]

Your other option is not actually an option, since you can't have more than one SQL statement in a query (in Access anyway). You can try adding DoEvents between the two Execute lines and see if that helps. Generally archiving isn't necessary anyway.