Access Transactions in Code with Commit and Rollback

Question

I've been asked to try to roll back some database changes if there was an error.

Before I even start trying to use a TRANSACTION with either COMMIT or ROLLBACK, could someone tell me if I can do the following in MS Access?

void Start() {
  try {
    AccessDatabaseOpen(); // Opens the access database
    foreach (File in FileList) {
      AccessTransactionStart(); // Starts the Transaction
      AccessWriteSectionDataFromFile();
      AccessWriteEmployeeDataFromFile();
      AccessWriteSomethingElseFromFile();
    } // go to next File in FileList
    AccessTransactionCommit();
  } catch {
    AccessTransactionRollback();
  } finally {
    AccessDatabaseClose();
  }
}

The syntax is crappy, but you should get the point: Can a routine in code start a transaction, call several other routines, and either commit or rollback the whole thing or is this idea make believe?

Thanks, Joe

Answer 1

Can a routine in code start a transaction, call several other routines, and either commit or rollback the whole thing

Yes, this is the basic idea of transaction handling and your outlined example would be a standard approach to deal with them from code. Details will vary depending on particular situation/needs and of course the database system used (e.g. nested transactions, scope, concurrency handling, etc.).

If a database abstraction layer is involved, check for specifics of that, as they often come with some implicit transaction handling that can often be configured by some settings/parameters.