[AccessD] Transactions around huge transformations

> I'm not *aware* of any system limitations imposed by Access itself ...

You are limited by the amount of disk on your working computer. So, if
you're doing huge transactions, you have to ensure you have a huge
amount of disk available. 

> I am also wondering whether I even want to do so since the failure 
> may be related to the instantaneous state of some table which 
> would be trashed if that table got rolled back.  IOW, how can I 
> see what went wrong if I roll back all the changes?

I use an ETL setup for my transforms. Data is extracted and transformed
into separate tables, and only loaded into production when the transform
is deemed successful. I ensure safety and traceability using these
steps:

Extract & Transform: 

I turn transactions OFF. Despite what help implies, using transactions
for lots of data slows things down considerably, and provides no real
benefit.

1. Do the extract and transform when no one is using the system. This
ensures the database is in a consistent state.
2. If you can, open the source data exclusively, so no one can get on
while you are doing your transformation.
3. Execute queries with dbFailOnError + dbSeeChanges as your execute
options. This ensures the process stops if there is an error, or someone
is making changes.
4. Use error logging. The error must give you enough description of what
went wrong for you to trace it. 
5. If verification is critical, write your own series of verification
queries to test your transform. If any records show up as invalid, log
them for analysis, and abort the load. You are going to have to write
these queries anyway, to make certain your transform works.

So, the rule is, it succeeds as a whole, or it fails as a whole. If it
fails, it never gets loaded into production, so no trusted data is
affected. 

Load: 

Once you have transformed and verified your data, use transactions to
load it into production, so if the append fails, you get the original
data back. 

-Ken