Commit Transaction in MS-Access fails to write to database

I'm using ADO Recordsets.
I implemented transactions around one particular set of queries that need to happen together.
The effect is that nothing ever commits.
It walks right through the code, hits the Commit, never hits the Rollback, but none of the inserts or updates ever appear in the database.

On review, it looks like I'm using ADO for the updates but DAO for the transactions.
On further review, they used CurrentProject.connection as the connection for the queries. Which looks like it comes from DAO too.

I think I want the transactions to be on the same connection as the inserts and updates.

So I need to get a connection object from ADODB. It feels like this should work, but it fails.

Private conn As ADODB.connection

' Set the connection if you want the same one each time
Public Sub SetConnection()
    If conn Is Nothing Then
        Set conn = New ADODB.connection
    End If
End Sub

' Close the connection when finished
Public Sub CloseConnection()
    Set conn = Nothing
End Sub

' Get the current connection or a fresh one
Public Function GetConnection() As connection
    If conn Is Nothing Then
' This fails with "Object Required' and looks like an empty string.
        Set GetConnection = New ADODB.connection
' This won't compile
'       Set GetConnection = ADODB.connection
' This won't compile with a Type Mismatch error.
'       Set GetConnection = New CurrentProject.connection 
' This looks like it works but nothing inside the transaction get committed.
'       Set GetConnection = CurrentProject.connection 
    Else
        Set GetConnection = conn
    End If
End Function

If I completely remove the Begin/Commit transaction statements, then everything gets written to the database correctly, unless there is an error of course.

What is the correct way to implement ADO transactions?

The code for the transactions is less interesting.

Public Sub Begin()
    If Not (conn Is Nothing) Then
        conn.BeginTrans
    End If
End Sub
Public Sub Commit()
    If Not (conn Is Nothing) Then
        conn.CommitTrans
    End If
End Sub
Public Sub Rollback()
    If Not (conn Is Nothing) Then
        conn.RollbackTrans
    End If
End Sub

from Recent Questions - Stack Overflow https://ift.tt/3tYMNdB
https://ift.tt/eA8V8J