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:
- BeginTrans - marks the start of a series of operations that should be considered as a single, atomic unit.
- CommitTrans - takes everything since the most recent BeginTrans and writes it to disk.
- 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:- Not all Recordsets support Transaction Processing. Check the Transactions Property of a Recordset to see whether it supports Transaction Processing.
- Transactions affect all changes to data in the Workspace.
- You can nest Transactions in Jet Databases up to 5 levels deep. Inner Transactions must be committed or rolled back before the surrounding ones.
- If you close a Workspace without explicitly committing its transactions, all pending Transactions are automatically back.
