Using Transactions in Your Applications

Microsoft Jet supports transactions through the BeginTrans, CommitTrans and Rollback methods of the Workspace object. The basic syntax is shown in the following table.

The following example changes the job title of all sales associates in the Employees table of the NorthwindTables database. After the BeginTrans method starts a transaction that isolates all of the changes made to the Employees table, the CommitTrans method saves the changes. Note that you can use the Rollback method to undo changes that you saved with the Update method. In this example, strDbPath is the path to the database:

Dim dbs As Database
Dim rst As Recordset
Dim wsp As Workspace
Dim strCriteria As String
	
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
strCriteria = "Title = ""Sales Associate"""

' Begin transaction.
wsp.BeginTrans
With rst
	' Search recordset for records matching criteria.
	.MoveFirst
	.FindFirst strCriteria
	' Edit matching records.
	Do While Not .NoMatch
		.Edit
		!Title = "Sales Representative"
		.Update
		.FindNext strCriteria
	Loop
End With

' Prompt user to save changes or roll back.
If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
	wsp.CommitTrans
Else
	wsp.Rollback
End If
rst.Close
dbs.Close

Important Because transactions are scoped to the Workspace object, transactions are global to the workspace, not to a specific database or recordset. If you perform operations on more than one database or recordset within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction.