DJK(John) Robinson
djkr at msn.com
Wed Nov 16 11:12:55 CST 2005
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