ならこれでよさそうだ。

Sub foo()
On Error Goto ErrHandler
　　Dim objConnection As ADODB.Connection
　　Set objConnection = New ADODB.Connection

　　Call objConnection.Open
　　Call objConnection.BeginTrans
　　Call objConnection.CommitTrans

ErrHandler:
　　If objConnection Is Nothing Then Exit Sub
　　If objConnection.State = ADODB.adStateClosed Then
　　　　Call objConnection.RollbackTrans
　　End If
　　If objConnection.State <> ADODB.adStateClosed Then
　　　　Call objConnection.Close
　　End If
End Sub