[Excel VBA] Using Transaction Processing (Commit/Rollback) for Access Data Entry

Introduction

When importing a large amount of data from Excel to Access, have you ever wondered, “What happens if an error occurs halfway through the process?” Usually, the process stops at the point of the error, leaving the data in a “half-finished” or corrupted state.

To prevent this, we use a mechanism called “Transaction Processing.”

This feature treats a series of operations as a single unit. The changes are finalized (Committed) to the database only if all operations succeed. If even one fails, all operations are cancelled (Rolled Back), returning the database to its previous state. This is crucial for maintaining data integrity.

In this article, I will explain how to implement transaction processing in VBA using DAO.

Important: Prerequisites

To use DAO features, specifically the Workspace object, you must enable a reference library. In the VBA Editor (VBE), go to Tools > References and check “Microsoft Office XX.0 Access database engine Object Library”.

Complete VBA Code

The following code adds multiple rows of Excel data to an Access table while processing transactions in batches of 100 records.

' Reference: Microsoft Office XX.0 Access database engine Object Library
Sub ImportDataWithTransaction()

    ' Declare variables
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sourceRange As Range
    Dim dbPath As String
    Dim commitInterval As Long
    Dim i As Long

    ' --- 1. Object and Variable Setup ---
    ' Database path
    dbPath = ThisWorkbook.Path & "\SalesDB.accdb"
    ' Excel data range
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:C501")
    ' Commit interval (number of records)
    commitInterval = 100
    
    ' Jump to "ErrorHandler" if an error occurs
    On Error GoTo ErrorHandler
    
    ' --- 2. Connect to Database and Start Transaction ---
    ' Get the default workspace
    Set ws = DBEngine.Workspaces(0)
    ' Open the database
    Set db = ws.OpenDatabase(dbPath)
    ' Open the target table
    Set rs = db.OpenRecordset("T_Sales_Log")
    
    ' Start the transaction
    ws.BeginTrans
    
    ' --- 3. Loop to Add Data ---
    For i = 1 To sourceRange.Rows.Count
        rs.AddNew
        rs!SalesID = sourceRange.Cells(i, 1).Value
        rs!ProductName = sourceRange.Cells(i, 2).Value
        rs!Quantity = sourceRange.Cells(i, 3).Value
        rs.Update
        
        ' Commit every specified number of records (Batch processing)
        If i Mod commitInterval = 0 Then
            ws.CommitTrans ' Finalize the current transaction
            ws.BeginTrans  ' Start the next transaction
        End If
    Next i
    
    ' --- 4. Process on Successful Completion ---
    ' Commit the final transaction if there are remaining records
    ws.CommitTrans
    
    MsgBox "All data has been successfully written."
    
    ' Skip the error handler since it finished normally
    GoTo CleanUp

ErrorHandler:
    ' --- 5. Process on Error ---
    ' Rollback the transaction (Undo operations)
    If Not ws Is Nothing Then ws.Rollback
    
    ' Display error details
    MsgBox "An error occurred. Processing was interrupted and rolled back." & vbCrLf & _
           "Error Row: " & i + 1 & vbCrLf & _
           Err.Description
           
    ' Select the Excel row where the error occurred
    Application.Goto sourceRange.Rows(i)

CleanUp:
    ' --- 6. Cleanup ---
    If Not rs Is Nothing Then rs.Close
    If Not db Is Nothing Then db.Close
    
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing

End Sub

Key Points of the Code

1. Getting the Workspace and Starting the Transaction

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans

Transactions are controlled through the Workspace object, which manages database sessions. We retrieve the default workspace using DBEngine.Workspaces(0) and call its .BeginTrans method to start a transaction. All data operations following this are temporary until CommitTrans is called.

2. Error Handling Mechanism (On Error GoTo)

On Error GoTo ErrorHandler
' ... Normal process ...
GoTo CleanUp

ErrorHandler:
    ' ... Error process ...
    ws.Rollback

On Error GoTo ErrorHandler forces the program to jump to the ErrorHandler: label if an error occurs. If the process finishes successfully, GoTo CleanUp skips the error handling section. This clearly separates success logic from failure logic.

3. Batch Processing (Split Commits)

If i Mod commitInterval = 0 Then
    ws.CommitTrans
    ws.BeginTrans
End If

Processing thousands of records in a single massive transaction can consume a lot of memory and degrade performance. Therefore, we split the process using commitInterval (100 records in this case), finalizing changes frequently with CommitTrans. The key is to immediately start the next transaction with .BeginTrans after committing.

4. Rollback on Error

ErrorHandler:
    ws.Rollback
    MsgBox "..."

In the error handler section, we first call the .Rollback method. This cancels all database operations performed since the last BeginTrans, keeping the database clean. We then notify the user of the error location to help identify the cause.

Summary

Transaction processing is an essential feature for applications handling databases to ensure data integrity.

• Manage transactions using the Workspace object.
• Enclose a block of operations with BeginTrans and CommitTrans.
• Detect errors with On Error GoTo and execute Rollback in the error handler.

By implementing this “All or Nothing” approach in VBA, you can build safer and more reliable data registration processes.