Error handling needed for Append and Delete queries?

[rudeboymcc]

Hi.

I'm trying to create an archive for my database. i'm going to append from multiple tables to another databse, adn then delete the originals.

THis is fine, but I'm worried somehting will happen during append and then all teh originals will be deleted.

what's teh best way to handle errors? is there a way to say if therre's an error, then undo all changes? I don't want it to only append half of them and stop half way.

thanks any help is appreciated.

 

[Dennisk]

Use transactions processing if you want to undo changes.

use Docmd.Execute YourSqlStatement, DBFailOnError

this will cause any error routines to execute, and is where to place your transaction rollback command.

 

[rudeboymcc]

wait so i have to make the rollback commands myself? Or does DBfailonerror do that for me?

I don't see how i can make the rollback commands myself as how would i know what's successfull and what isn't?

 

[Pat Hartman]

Never run the delete query first. Always run the append query and THEN the delete query.

 

[Dennisk]

look up transaction processing in the help file.

it is the transaction.rollback that does it all for you, in a transparent manner.