A transaction can be defined as a related set of operations that read and modify data in a single batch. Every transaction should follow the ACID rule, where ACID stands for atomic, consistent, isolated, and durable. A transaction provides atomicity to a series of data modifications to one or more recordsets within a connection, allowing all of the changes to take place at once, or not at all.

You can use BeginTrans() and CommitTrans() methods of ADODB.Connection object to begin a new transaction or to commit a pending transaction. Method RollbackTrans() aborts any pending transaction.

But before you use these methods of Connection object, you should ensure that your provider supports transactions. Otherwise calling these methods against a provider that doesn’t support transactions will generate an error. You can use “Transaction DDL” property of the connection object to check if your provider supports Transactions. Here is a simple method which can be used to verify if your Provider supports Transactions or not. This method takes in a connection object as parameter and displays a message box about the Transaction support.

 Private Sub CheckTransactionSupport(ByVal _	oConnection As ADODB.Connection)	' Name of the Property	Const DBPROP_TRANSACTION_SUPPORT As String _		= "Transaction DDL"	' Values returned by the provider for the property	Const DBPROPVAL_TC_NONE As Long = &H0	Const DBPROPVAL_TC_DML As Long = &H1	Const DBPROPVAL_TC_DDL_COMMIT As Long = &H2	Const DBPROPVAL_TC_DDL_IGNORE As Long = &H4	Const DBPROPVAL_TC_ALL As Long = &H8	On Error GoTo ErrorHandler	' Check Transaction support property of	' connection object.	' It can have one of the following values	Select Case oConnection.Properties _		(DBPROP_TRANSACTION_SUPPORT)		Case DBPROPVAL_TC_NONE			MsgBox "Transactions are not supported"		Case DBPROPVAL_TC_DML			MsgBox "Transactions can only contain " & _				"DML statements. " & vbCrLf & _				"DDL statements within a transaction " & _				"cause an error"		Case DBPROPVAL_TC_DDL_COMMIT			MsgBox "Transactions can contain " & _			"DML statements. " & vbCrLf & _			"DDL statements within a transaction " & _			"cause the transaction to be committed"		Case DBPROPVAL_TC_DDL_IGNORE			MsgBox "Transactions can only " & _				"contain DML statements. " & vbCrLf & _				"DDL statements within a transaction " & _				"are ignored"		Case DBPROPVAL_TC_ALL			MsgBox "Transactions can contain " & _				"both DDL and DML statements"	End Select	Exit SubErrorHandler:	' Display error message	MsgBox "Error Number = " & _		Err.Number & ", Description = " & Err.Description, _		vbCritical, "Check Transaction Support Error"End Sub