8,834
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.
Expand|Select|Wrap|Line Numbers
- 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
- 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 rolled back.
