VB6 ADO Connection - How to Check if in Transaction?

Question

Is there any way to tell, using just the ADODB.Connection object, whether or not it is currently involved in a transaction?

I'd love to be able to test this on the connect object itself, without relying on keeping a boolean next to it updated.

Answer 1

It looks like you can check the ADO state. http://msdn.microsoft.com/en-us/library/ms675068%28v=VS.85%29.aspx

You probably already know this other part but I'll post it anyway.

This explains how the transactions work with ADO in VB. http://support.microsoft.com/kb/198024

Answer 2

You can't unless you track it yourself. The connection object doesn't have a property dealing with transaction state. You'll have to have your proc set a flag in another table/settings area if you HAVE to have it (which can be problematic if unhandled errors occur and the state flag ever gets "stuck" with an invalid status, you need come up with a valid "timeout" or override to ignore/kill/overwrite the previous).

Answer 3

The BeginTrans method can be used as a function that returns the nesting level of the transaction. If you create a property to store this you can check it where ever you need to to see if it is greater than 0. When you commit or rollback you will need to decrement the property yourself.

Private m_TransLevel As Long

Public Property Get TransactionLevel() As Long
    TransactionLevel = m_TransLevel
End Property
Public Property Let TransactionLevel(vLevel As Long)
    m_TransLevel = vLevel
End Property

Public Sub SaveMyData()

    TransactionLevel = adoConnection.BeginTrans()
    ...

End Sub

You could also adapt the return value to work inside a function that returns True/False if the level > 1. I don't like this as well, but it would look something like this (without error handling)

Public Function IsConnectionInsideTransaction(ByVal vADOConnection as ADOBD.Connection) As Boolean
    Dim intLevel As Integer

    If vADOConnection.State = AdStateOpen Then
        intLevel = vADOConnection.BeginTrans()
        IsConnectionInsideTransaction = (intLevel > 1)
        vADOConnection.RollbackTrans
    End If

End Function