>This question came up in Access, but I think that it is important and relevant to any database programming, and might be answered in the same general way.
>
>I have many tables that contain records with a Group field. The users like to archive older groups in another database to get them out of the way and speed up queries. I have a tool that archives and restores groups this way:
>
>1. Archive and restore form lists all groups in main and archive databases using multi-select list boxes. The user can choose groups to archive or restore and click a button. Selected groups are stored in a table "selectedgroups".
>
>2. A table listing all tables that have a group field is opened and scanned. The main database connects the appropriate table in the archive database and does an append query that copies the records WHERE groupname IN (SELECT groupname FROM selectedgroups). Then it disconnects the archive table.
>
>3. It goes through each table and does a delete query with the same WHERE condition.
>
>4. The process is reversed when groups are restored.
>
>The problem comes when there is an error during this long process. A group might be copied in some tables and not in others. It might get copied to the archive and then not deleted from the original. Ideally, an error would result in reversal of the whole process, but that could easily cause the same error. The errors we sometimes get result in duplicate records appearing for certain groups that have been archived and later restored. Some of these tables have memo fields, so I can't use SELECT DISTINCT to weed that out.
>
>One way to reduce errors would be to open all the relevant tables exclusively before beginning, so we don't bump into locked records. It makes sense to do so for this sort of task. But it is about a dozen tables, and could be more in the future. How many is too many?
>
>Can I solve all this by wrapping the whole thing in one giant TRANSACTION? Is there a limit to how much I should put in the TRANSACTION? I understand that this part of the question would be answered differently in an Access forum. I don't think that Access has vfp-style transaction processing. It would be different again if it was SQL-Server or Oracle, I suppose.
>
>I confess that I don't understand error trapping real well. Error handlers usually are designed to shut things down quickly and cleanly, but we can't do that here. Perhaps the answer is not to clean up after an error here, but to put up warnings and have the users call me. I suppose that an archiving tool should not be in routine use, but I can see that they have been using it a lot.

Here's the worst solution candidate first: have the process log everything, and have them call you whenever it gets wrong :).

Now, seriously, you may do some logging while this process is running, namely the tables and record PKs, so you know which ones got copied and which ones didn't. Then, another thing which would need some intricate dancing around each table - check for the existence of a group id in the target tables first; if it's already there, there's something wrong and just issue a warning and skip that group. Then, before deleting, check that the record to be deleted is actually copied into the target table.

I know, this is almost impossible to do using just SQL, queries etc - it means lots of If Seek(), Scan and record-by-record tiny-stepping, and may lead to nice spaghetti code. I can't imagine any elegant code which could deal with such issues. It's IBM - immer besser manuel. But then, I'm just too deep in FPD to have some experience with dealing with such transactions on a higher level.