DAO Transaction Processing - What is it?

[ADezii]

Transaction Processing is a Database term that refers to the process of grouping changes to your data into a batch that is treated as a single, atomic unit. Either the entire batch of transactions succeeds, or they all fail. A typical illustration of a Transaction is the transferring of data from one account to another in a banking application. You wouldn’t want your originating account debited a specific amount, have an error occur, and not have your ending account credited.

When referring to DAO Transactions, we will be dealing with 3 critical Methods of the Workspace Object. ADO Transactions, although very similar, will be covered in another Tip. These 3 Methods are briefly mentioned below:

1. BeginTrans - marks the start of a series of operations that should be considered as a single, atomic unit.
2. CommitTrans - takes everything since the most recent BeginTrans and writes it to disk.
3. Rollback - the opposite of CommitTrans; it undoes all your changes back to the last CommitTrans. The critical word here is all.

In its basic, sketal format, DAO Transaction Processing looks something like this:

On Error GoTo Err_Handler
 
Dim wrkCurrent As DAO.Workspace
Dim blnInTrans As Boolean      'are we in a Transaction?
 
blnInTrans = False      'not in a Transaction yet
Set wrkCurrent = DAO.DBEngine.Workspaces(0)
'...
 
wrkCurrent.BegingTrans
blnInTrans = True      'presently in a Transaction
 
'make all data modifications/changes here
 
wrkCurrent.CommitTrans
blnInTrans = False      'changes committed without an Error, Transaction is complete
 
'...
Err_Handler:
  If blnInTrans Then      'was the Transaction successfully completed, or does it need to be Rolled back?
    wrkCurrent.Rollback
  End If
  'continue Error Processing if necessary

Several issues when using DAO Transaction Processing:

1. Not all Recordsets support Transaction Processing. Check the Transactions Property of a Recordset to see whether it supports Transaction Processing.
2. Transactions affect all changes to data in the Workspace.
3. You can nest Transactions in Jet Databases up to 5 levels deep. Inner Transactions must be committed or rolled back before the surrounding ones.
4. If you close a Workspace without explicitly committing its transactions, all pending Transactions are automatically rolled back.

[NeoPa]

A question related to this was posted and can be found at Transactions - Error Handler.