Transaction Behavior

Often, to enforce data integrity, a set of operations must be considered as a single unit. For example, the transfer of funds from one bank account to another consists of two operations: entering a debit in one account and a matching credit in the other account. In practice, either both operations must succeed or neither operation should be processed.

In this situation, the debit and credit operations are a single unit, or a transaction. A transaction is a set of operations that are committed (saved) if and only if all of the operations succeed. If any of the operations fail, all of the operations that succeeded are rolled back (canceled), and the data is returned to the state it was in before the transaction began.

DAO supports three transaction methods: one for starting, one for committing, and one for rolling back a transaction:

• The BeginTrans method begins a new transaction.
  
  
• The CommitTrans method commits all changes made to data by the most recent BeginTrans method.
  
  
• The Rollback method rolls back, or cancels, all changes made to data by the most recent BeginTrans method.

BeginTrans, CommitTrans, and Rollback are all methods of the Workspace object.

On most servers, transactions hold locks that prevent other users from updating or even reading data affected by the transaction until it’s committed or rolled back. You should therefore keep your transactions as short as possible and avoid beginning a transaction and then waiting for the user’s response before completing it.

The following code shows a transfer using two common update operations, a withdrawal and a deposit. The transfer occurs in the context of a transaction. If either operation fails, the transaction makes sure that the operation as a whole fails.

Sub IncreaseRoyalty(strDbPath As String)
	Dim wrk As Workspace, dbs As Database
	Dim qdf As QueryDef

	On Error GoTo Err_IncreaseRoyalty

	Set wrk = DBEngine.Workspaces(0)
	Set dbs = wrk.OpenDatabase(strDbPath)

	' Begin the transaction.
	wrk.BeginTrans

	' Create a temporary SQL pass-through query.
	Set qdf = dbs.CreateQueryDef("")
	' Set QueryDef properties and execute.
	With qdf
		.Connect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
		.ReturnsRecords = False
		.SQL = "UPDATE Roysched SET Royalty = " & _
			"Royalty + 2 WHERE HiRange < 6000"
		' Execute method will fail if error occurs.
		.Execute dbFailOnError
	End With
	' Commit the transaction.
	wrk.CommitTrans
	
Exit_IncreaseRoyalty:
	On Error Resume Next
	dbs.Close
	Set dbs = Nothing
	Exit Sub

Err_IncreaseRoyalty:
	' If one operation fails, roll them all back.
	wrk.Rollback
	VerboseErrorHandler
	MsgBox "Transaction rolled back!"
	Resume Exit_IncreaseRoyalty
End Sub

How Microsoft Jet Uses ODBC Transactions

When your DAO code does not use transaction methods explicitly, Microsoft Jet will use ODBC in auto-commit mode. In this mode, each SQL statement sent to the server has an implicit transaction around it; that is, the SQL statement has an immediate effect on the ODBC data and cannot be rolled back. Microsoft Jet stops using auto-commit mode and uses explicit ODBC transactions with:

• SQL statements that modify data (for example, UPDATE, INSERT, or APPEND).
  
  
• Explicit DAO transaction methods.

When using bulk operation SQL statements such as UPDATE, INSERT, and APPEND, Microsoft Jet places a transaction around the operation so that it can succeed or fail as a single entity. This is necessary because one such Microsoft Jet SQL statement may correspond to many individual server statements.

DAO Transactions

Multiple concurrent transactions on Recordset objects against a single server are actually a single transaction because a single connection is being used to service updates for both dynasets. You should structure your transactions so that they do not overlap; transactions are intended to be atomic units.

If the server supports transactions at all, Microsoft Jet assumes only single-level support; that is, no nesting of transactions. Therefore, if your application code nests transactions, only the outermost BeginTrans, CommitTrans, and Rollback methods are sent to the server; nested transactions are ignored without warning.

These transaction semantics also apply to SQL pass-through queries that modify server data, so explicit transactions within the pass-through queries cannot be used.

See Also For more information on SQL pass-through queries, see “Using SQL Pass-Through Queries” earlier in this chapter.