Can ADO be used for BEGINTRANS COMMIT TRANS (3 Viewers)

[JohnPapa]

Can ADO be used to handle transactions, where the transactions are committed only if all transactions are executed. I have found examples with DAO.

 

[sonic8]

Yes.

BeginTrans, CommitTrans, and RollbackTrans methods (ADO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[JohnPapa]

Yes.

BeginTrans, CommitTrans, and RollbackTrans methods (ADO)

Office developer client VBA reference documentation

learn.microsoft.com

Thanks. I have seen this. Was looking for an example.

 

[cheekybuddha]

Pseudo example

Function DoTransaction() As Boolean
On Error GoTo ErrHandler

  Dim ret As Boolean
  Dim blInTrans As Boolean
  Dim strSQL As String
 
  With CurrentProject.Connection
    .BeginTrans
    blInTrans = True
      strSQL = "INSERT INTO ..."
      .Execute strSQL, , adCmdText + adExecuteNoRecords
      strSQL = "UPDATE ..."
      .Execute strSQL, , adCmdText + adExecuteNoRecords
    .CommitTrans
  End With
  ret = True
 
ExitHere:
  DoTransaction = ret

ErrHandler:
  If blInTrans Then .RollbackTrans
  MsgBox Err.Number
  Resume ExitHere

End Function

 

[JohnPapa]

Pseudo example

Function DoTransaction() As Boolean
On Error GoTo ErrHandler

  Dim ret As Boolean
  Dim blInTrans As Boolean
  Dim strSQL As String
 
  With CurrentProject.Connection
    .BeginTrans
    blInTrans = True
      strSQL = "INSERT INTO ..."
      .Execute strSQL, , adCmdText + adExecuteNoRecords
      strSQL = "UPDATE ..."
      .Execute strSQL, , adCmdText + adExecuteNoRecords
    .CommitTrans
  End With
  ret = True
 
ExitHere:
  DoTransaction = ret

ErrHandler:
  If blInTrans Then .RollbackTrans
  MsgBox Err.Number
  Resume ExitHere

End Function

Many thanks cheekybuddha. I have made some minor changes and I include below the code you provided:

Function funDoTransaction() As Boolean
On Error GoTo ErrHandler

Dim lngDum As Long

Dim blnReturn As Boolean
Dim blInTrans As Boolean
Dim strSQL As String

blInTrans = False
 
With CurrentProject.Connection
      .BeginTrans
      blInTrans = True
      strSQL = " INSERT INTO tblDum (lngID) VALUES (5);"
   
        .Execute strSQL, adCmdText + adExecuteNoRecords
   
      'Uncomment to trigger an error
      'lngDum = 1 / 0
   
      .CommitTrans
End With

blnReturn = True
 
ExitHere:
    funDoTransaction = blnReturn
    Exit Function

ErrHandler:
    If blInTrans Then
        CurrentProject.Connection.RollbackTrans
        MsgBox ("Error Number: " & Err.Number & ". The transaction has been rolled back")
        blnReturn = False
    End If
    Resume ExitHere

End Function

 

[JohnPapa]

As a last question, since I have never used BeginTrans etc, to anyone who has used it, did you find any problems?

 

[JohnPapa]

Are there restrictions in what you insert between BeginTrans and CommitTrans?

If for example I define a recordset, loop through the recordset and change values, can these changes get rolled back?

 

[bbfromgb]

For Access transactions checkout sonic8's recommendations on transactions on codekabinett.com ((sorry, cannont post the full link)

 

[jdraw]

Here's the link for codeKabinett Transaction example He (sonic8 /Philipp Stiefel) is using DAO.

 

[Gasman]

Can ADO be used to handle transactions, where the transactions are committed only if all transactions are executed. I have found examples with DAO.

ChatGPT seems to say yes?
So here is it's example.

Sub UseADOTransaction()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim connectionString As String
    
    ' Define your connection string
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=C:\Path\To\Your\Database.accdb;"
    
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    On Error GoTo ErrorHandler
    
    ' Open the connection
    conn.Open connectionString
    conn.BeginTrans ' Start the transaction
    
    ' First SQL command
    cmd.ActiveConnection = conn
    cmd.CommandText = "INSERT INTO Customers (CustomerName) VALUES ('Alice')"
    cmd.Execute

    ' Second SQL command
    cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, Date())"
    cmd.Execute

    conn.CommitTrans ' Commit if all successful
    MsgBox "Transaction committed successfully."

    GoTo Cleanup

ErrorHandler:
    conn.RollbackTrans ' Rollback on error
    MsgBox "Transaction failed. Rolled back." & vbCrLf & "Error: " & Err.Description

Cleanup:
    If conn.State = adStateOpen Then conn.Close
    Set cmd = Nothing
    Set conn = Nothing
End Sub