>
BeginTrans, CommitTrans, Rollback Methods
Applies To
Workspace Object.
Description
The transaction methods manage transaction processing during a session defined by a Workspace object as follows:
BeginTrans begins a new transaction.CommitTrans ends the current transaction and saves the changes.Rollback ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.
Syntax
workspace.BeginTrans | CommitTrans | Rollback
The transaction methods have this part.
| Part | Description |
|
| workspace | A variable of an object data type identifying the Workspace. |
Remarks
You use these methods with a Workspace object when you want to treat a series of changes made to the databases in a session as one unit.
Typically, you use transactions to maintain the integrity of your data when you must update records in two or more tables and make sure changes made are completed (committed) in all tables or none at all (rolled back). For example, if you transfer money from one account to another, you might subtract an amount from one and add the amount to another. If either update fails, the accounts no longer balance. Use the BeginTrans method before updating the first record, and then, if any subsequent update fails, you can use the Rollback method to undo all of the updates. Use the CommitTrans method after you successfully update the last record.
Caution
Within one Workspace object, transactions are always global to the Workspace and aren't limited to only one database or recordset. If you perform operations on more than one database or recordset within a Workspace transaction, the Rollback method restores all operations on those databases and recordsets.
After you use CommitTrans, you can't undo changes made during that transaction unless the transaction is nested within another transaction that is itself rolled back. You can have up to five levels of transactions open at once in a single Workspace object by using multiple, nested combinations of BeginTrans and CommitTrans or Rollback. If you nest transactions, you must save or roll back the current transaction before you can save or roll back a transaction at a higher level of nesting. If you want to have simultaneous transactions with overlapping, non-nested scopes, you can create additional Workspace objects to contain the concurrent transactions.
If you close a Workspace object without saving or rolling back any pending transactions, the transactions are automatically rolled back.
If you use the CommitTrans or Rollback methods without first using the BeginTrans method, an error occurs.
Some databases may not support transactions, in which case the Transactions property of the Database object or Recordset object is False. To make sure that the database supports transactions, check the value of the Transactions property of the Database object before using the BeginTrans method. If you are using a Recordset object based on more than one database, check the Transactions property of the Recordset object. If a recordset is based entirely on Microsoft Jet database engine tables, you can always use transactions. Dynaset-type Recordset objects based on tables created by other database products, however, may not support transactions. For example, you can't use transactions in a Recordset based on a Paradox table. In this case, the Transactions property is False. If the Database or Recordset doesn't support transactions, the methods are ignored and no error occurs.
When using external ODBC SQL databases, you can't nest transactions.
Tip
In addition to performing transactions to maintain data integrity, you can often improve the performance of your application by breaking operations that require disk access on your database into transaction blocks. This ensures buffering of your operations and may greatly reduce the number of times a disk is accessed.
Note
When you begin a transaction, the Jet database engine records its operations in a file kept in the directory specified by the TEMP environment variable on the workstation. If the transaction log file exhausts the available storage on your TEMP drive, the Jet database engine triggers a trappable error (2004). At this point, if you use CommitTrans, an indeterminate number of operations are committed but the remaining uncompleted operations are lost, and the operation has to be restarted. Using a Rollback method releases the transaction log and rolls back all operations in the transaction.
See Also
Close Method, CreateWorkspace Method, Refresh Method, Transactions Property.
Example
This example changes the job title of all sales representatives in the Employees table of the database. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved using the Update method.
One or more table pages remain locked while the user decides whether or not to accept the changes. For this reason, this technique isn't recommended but shown only as an example.
Sub ChangeTitle
Dim strName As String, strMessage As String, strPrompt As String
Dim wspDefault As Workspace, dbsNorthwind As Database
Dim rstEmployees As Recordset
strPrompt = "Change title to Account Executive?"
Set wspDefault = DBEngine.Workspaces(0) ' Get default Workspace.
' Get current database.
Set dbsNorthwind = wspDefault.OpenDatabase("Northwind.mdb")
' Open table.
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees", _
dbOpenTable)
wspDefault.BeginTrans ' Start of transaction.
rstEmployees.MoveFirst
Do Until rstEmployees.EOF
If rstEmployees![Title] = "Sales Representative" Then
strName = rstEmployees![LastName] & ", " & _
rstEmployees![FirstName]
strMessage = "Employee: " & strName & vbCrLf & vbCrLf
If MsgBox(strMessage & Prompt, vbQuestion + vbYesNo, _
"Change Job Title") = YES Then
rstEmployees.Edit ' Enable editing.
rstEmployees![Title] = "Account Executive"
rstEmployees.Update ' Save changes.
End If
End If
rstEmployees.MoveNext ' Move to next record.
Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo, _
" Save Changes") = YES Then
wspDefault.CommitTrans ' Commit changes.
Else
wspDefault.Rollback ' Undo changes.
End If
rstEmployees.Close ' Close table.
dbsNorthwind.Close
End Sub
Example (Microsoft Access)
The following example changes the job title of all sales representatives in an Employees table. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved using the Update method.
One or more table pages remain locked while the user decides whether or not to accept the changes. Consequently, this technique isn't recommended but only shown as an example.
Sub ChangeTitle()
Dim wsp As Workspace, dbs As Database, rst As Recordset
Dim strName As String, strMessage As String, strPrompt As String
strPrompt = "Change title to Account Executive?"
' Get default Workspace.
Set wsp = DBEngine.Workspaces(0)
' Get current database.
Set dbs = CurrentDb
' Create table-type Recordset object.
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
' Start of transaction.
wsp.BeginTrans
rst.MoveFirst
Do Until rst.EOF
If rst![Title] = "Sales Representative" Then
strName = rst![LastName] & ", " & rst![FirstName]
strMessage = "Employee: " & strName & vbCrLf & vbCrLf
If MsgBox(strMessage & strPrompt, vbQuestion + vbYesNo, _
"Change Job Title") = vbYes Then
' Enable editing.
rst.Edit
rst![Title] = "Account Executive"
' Save changes.
rst.Update
End If
End If
' Move to next record.
rst.MoveNext
Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo, _
"Save Changes") = vbYes Then
wsp.CommitTrans ' Commit changes.
Else
wsp.Rollback ' Undo changes.
End If
End Sub