[AccessD] Transactions around huge transformations

Hi John

Experience, yes, though it was some years ago and I can't now recall details
about the volume of operations within each transaction.  Lots of appends to
multiple tables, at least - can't remember what else.  I think it must have
been on A97.

I'm not *aware* of any system limitations imposed by Access itself, but that
may just be ignorance!

My application was handling multiple batches of potential updates to the
database, and each batch *might* get rejected in its entirety as a result of
validation done on-the-fly while appending/updating the main tables.  But I
continued the processing of a batch even after finding an error, in the hope
of finding *all* errors, before the transaction was finally rolled back.
What was interesting (to me, at least) was that I logged any errors in
another table which therefore had to be appended/updated in a separate
workspace, to avoid being affected by the transaction roll-back on the first
one.  I did find a bug in Access (confirmed, thank goodness, by Shamil!)
relating to Access's reporting of the number of records in a table at some
point, but we concluded that it was essentially harmless - provided the user
didn't see the apparent discrepancy!  The resulting database ran happily
every week, and may still be doing so.  End of lamp-swinging.

Of course you'll take a copy first ...

Good luck
John


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: 16 November 2005 15:49
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Transactions around huge transformations


I am still working on the data migration (transform) application for a
specific client.  The transform consists of archiving a bunch of tables (20
or so), deleting all records in these tables, then appending new data in.
What I am wondering is whether transactions can be used to wrap this large a
sequence of operations to cause a rollback in the event anything goes wrong.


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?

So, does anyone have any experience in wrapping dozens of operations in a
single transaction?

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com