Commit Transaction in MS-Access fails to write to database

Question

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

score 0 · Accepted Answer · answered Apr 27 '21 at 01:08

0

Don't have access for testing, but try this:

Private conn As ADODB.connection


Public Sub CloseConnection()
    Set conn = Nothing
End Sub

'Make sure you specify return type as ADODB.Connection
Public Function GetConnection() As ADODB.Connection
    If conn Is Nothing Then Set conn = CurrentProject.Connection
    Set GetConnection = conn
End Function

answered Apr 27 '21 at 01:08

Tim Williams

154,628
8
97
125

No errors, but it doesn't commit anything either. Confirmed that all queries and BeginTrans are all using the same connection. – BWhite Apr 28 '21 at 16:23
I'm giving you credit for the correct answer because it appears to be specifying the return type that allowed it to work this time. – BWhite Apr 28 '21 at 19:35

BWhite · Answer 2 · 2021-04-28T19:36:24.823

I don't know why using BeginTrans doesn't work when using the connection from CurrentProject.Connection, but it doesn't.

Here is the final solution.
Use GetConnection() everywhere you want a connection. The exception would be items that you do NOT want rolled back in case of failure, like logging.
Call Begin, Commit and Rollback, as usual. Begin will create a persistent connection that the others will use. After Commit, it will go back to using the project connection. Or you can create a fresh ADO connection with each query.

Private conn As ADODB.connection

'Make sure you specify return type as ADODB.Connection or it doesn't work
Public Function GetConnection() As ADODB.connection
    If conn Is Nothing Then
'        Set GetConnection = New ADODB.connection
        Set GetConnection = CurrentProject.connection
    Else
        Set GetConnection = conn
    End If
End Function

Public Sub SetConnection()
On Error GoTo ErrHandler:
    If conn Is Nothing Then
'        Set conn = CurrentProject.connection
        Set conn = New ADODB.connection
        conn.Open GetConnectionString
    End If
ErrHandler:
    Set conn = Nothing
End Sub

Public Sub CloseConnection()
    Set conn = Nothing
End Sub

Public Sub Begin()
    SetConnection
    If Not (conn Is Nothing) Then
        conn.BeginTrans
    End If
End Sub

Public Sub Commit()
    If Not (conn Is Nothing) Then
        conn.CommitTrans
        CloseConnection
    End If
End Sub

Public Sub Rollback()
    If Not (conn Is Nothing) Then
        conn.RollbackTrans
        CloseConnection
    End If
End Sub

' Can be any table, just need to get the conn string
Public Function GetConnectionString() As String
    GetConnectionString = CurrentDb.TableDefs("System Log").Connect
End Function

Commit Transaction in MS-Access fails to write to database

2 Answers2

Linked