Can't open a connection to create a transaction

Question

In MS-Access, I'm trying to get a connection so that I can run queries within a transaction.

Stripped down, the routine looks like this:

Public Sub SetConnection()
    Dim ConnectionString As String
    ConnectionString = CurrentDb.TableDefs("RandomTable").Connect
    If conn Is Nothing Then
        Set conn = New ADODB.Connection
        PostToLog "SetConnection()", "Set Connection to " & ConnectionString
        conn.Open ConnectionString     '  Fails here
   '     conn.Open   '  Gives the same error without specifying the connection
    End If
    Exit Sub

I can see that the connection string is exactly that set in the Linked Table Manager.
Then I use it like this

Public Sub Begin()
    PostToLog "Begin()", "Start Begin"
    SetConnection
    If Not (conn Is Nothing) Then
        PostToLog "Begin()", "Begin"
        conn.BeginTrans
    End If
End Sub
Public Sub Commit()
    PostToLog "Commit()", "Start Commit"
    If Not (conn Is Nothing) Then
        PostToLog "Commit()", "Committing"
        conn.CommitTrans
    End If
    CloseConnection
End Sub
Public Function GetConnection() As ADODB.Connection
' Use GetConnection() everywhere you want a connection. If in a transaction, these queries will automatically be included.
' The exception would be items that you do NOT want rolled back in case of failure, like logging.
    If conn Is Nothing Then
'        Set GetConnection = New ADODB.Connection
        Set GetConnection = CurrentProject.Connection
    Else
        Set GetConnection = conn
    End If
End Function

But the connection fails to open, conn isn't set, and everything happens outside a transaction.

The error is

Unspecified error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified: ConnectionString: ODBC;DSN=....     

I've also tried

ConnectionString = CurrentProject.Connection.ConnectionString  

but that looked like the connection string to the front end Access database, not the back end.
I can't find any excuse for why the linked tables can find the DSN, but can't when I create the connection.

Begin, Commit, Rollback are used like so....

objSQL.Begin   ' Start transaction
' do some stuff that uses the conn connection
objSQL.Commit
Err: 
objSQL.Rollback 

Update: Using conn = CurrentProject.Connection instead of creating a new ADODB connection appears to work. No errors are thrown.
But the changes do not appear, as if they all get rolled back.
Postgres doesn't allow for a dirty read, so I can't tell if the changes never happened or if they do and get reversed. And if they do get reversed, I can't tell if that is before or after the Commit or what exactly triggers it. I can see the SQL statements appearing to get executed on the Postgres connection.

I'm going to try exploring a little more what happens when I have a proper connection string, as demonstrated by @HansUp