ADODB.command.Execute fails if I use a new connection

If I create a new connection, the first call to SQL, whatever it is, fails.

        ConnectionString = CurrentProject.Connection.ConnectionString
        Set connection = New ADODB.connection

        connection.Open ConnectionString  ' Fails here now
    End If
    If connection.STATE = adStateOpen Then
        MsgBox "Success"
    Else
        MsgBox "Failure to communicate"
    End If
    connection.Execute "Select 1 from [System Log] where 1 = 0", , adCmdText Or adExecuteNoRecords

Fails with "Object variable or With block variable not set"

If I just use

 Set connection = CurrentProject.connection

it works fine.

Connection string now looks like
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Apps\AccessDB.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\user\AppData\Roaming\Microsoft\Access\System.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\16.0\Access\Access Connectivity Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

Solution

Normally, when you create a new connection, you are trying to connect to external data and ADO works great for that.
In this case, I wanted to work through the existing local and linked tables, just with a separate connection so I could have everything in a transaction.
I didn't want to use the existing connection because there are some things that I didn't want to get rolled back on failure, like logging.

In the end, I couldn't get this to work.

I considered moving everything to using DAO to make it easy to access the local data, but that would have been a massive and risky change.

The final version went the other way and switched the logging to using DAO and CurrentDB, while everything else continued to use ADO and CurrentProject.Connection. This way I just set the transaction on the connection and no new ADODB needed.