Attribute VB_Name = "basADOeditTrans"
Option Compare Database
Option Explicit

Sub ADOeditTrans()

'Declare your connection and recordset
Dim cnConn As ADODB.Connection
Dim rsTable As ADODB.Recordset

'Set your connection to the current project
'Set your table to a new ADO recordset
Set cnConn = CurrentProject.Connection
Set rsTable = New ADODB.Recordset

'Open your table - in this case tblNames
rsTable.Open "tblNames", cnConn, adOpenDynamic, adLockOptimistic, adCmdTable

Dim i As Integer    'record counter
i = 0   'initialize it as 0

cnConn.BeginTrans   'This begins the transaction

'Move to the first record and loop through them to the end
rsTable.MoveFirst
Do Until rsTable.EOF

    If rsTable!strLastName = "Jane" Then 'changing each record of JANE to JONES
        rsTable!strLastName = "Jones"
        i = i + 1   'add one everytime a record is changed.
    End If
    
rsTable.Update
rsTable.MoveNext
Loop

'Confirm the Edit or Choose to Cancel
If MsgBox("You are about to change " & i & " records." & vbNewLine & _
    "Do you wish to complete the Transaction?", vbQuestion + vbYesNo _
    , "Confirm") = vbYes Then
cnConn.CommitTrans
    MsgBox "Changes were made successfully!"
Else
cnConn.RollbackTrans
    MsgBox "No changes were made."
End If

'Clean up your variables
rsTable.Close
cnConn.Close
Set rsTable = Nothing
Set cnConn = Nothing

End Sub