Cascading Transaction Rollbacks

[winshent]

Can anyone tell me if transaction success or fail can cascade up if using the same connection.

Below I have some sample code. Sub1 creates an ADO connnection, executes some SQL against it and the parses the connection to Sub2.

If there is an error in sub2 when executing the SQL, will both transactions be caught by the error handling in Sub1 and rollback ?

Option Compare Database
Option Explicit

Private Sub sub1()

    On Error GoTo PROC_ERR
       
    Dim objConn As New ADODB.Connection
    Dim SQL As String
    
    With objConn
        .ConnectionString = gstrGetConnectionString
        .Open
        .BeginTrans
    
        SQL = "INSERT INTO Table1 ( Column1 ) SELECT Column1 FROM Table2 " & _
              "WHERE Column1 NOT IN (SELECT Column1 FROM Table1)"
        
        .Execute SQL, dbFailOnError

        Call sub2(objConn)
        
        .CommitTrans
        .Close
    End With
    
PROC_EXIT:
    On Error Resume Next
    
    If objConn Is Nothing Then
        objConn.Close
        Set objConn = Nothing
    End If
    
    Exit Sub

PROC_ERR:
    MsgBox Err.Description
    objConn.RollbackTrans
    Resume PROC_EXIT

End Sub

Private Sub sub2(ByRef pobjConn As ADODB.Connection)

    On Error GoTo PROC_ERR
       
    Dim SQL As String
    
    With pobjConn
    
        SQL = "INSERT INTO Table1 ( Column1 ) SELECT Column1 FROM Table2 " & _
              "WHERE Column1 NOT IN (SELECT Column1 FROM Table1)"
        
        .Execute SQL, dbFailOnError

    End With
    
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT

End Sub

 

[Fran Lombard]

Based on your code - I would say yes both statements should be rolled back. Since both statements are on the same connection object and the transaction has not yet been committed both statements should be rolled back. I would test this by forcing an error in the second sub and check if the first statement is rolled back as expected.

--Update--
In double checking your code I noticed you only had the rollback code in the first sub to handpe an error that was encountered in that sub. You probably would not end up in the error handler if the error occurred in the second sub. You probably should either add the rollback to the second sub or change the second sub to a function that returns a Boolean "ProcessSuccess" so sub 1 can make a decision to commit or rollback.