IsolationLevel and Mode Properties Example (VB)

IsolationLevel and Mode Properties Example (VB)

This example uses the Mode property to open an exclusive connection, and the IsolationLevel property to open a transaction that is conducted in isolation of other transactions.

Public Sub IsolationLevelX()

    Dim cnn1 As ADODB.Connection
    Dim rstTitles As ADODB.Recordset
    Dim strCnn As String

    ' Assign connection string to variable.
    strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "

    ' Open connection and Titles table.
    Set cnn1 = New ADODB.Connection
    cnn1.Mode = adModeShareExclusive
    cnn1.IsolationLevel = adXactIsolated
    cnn1.Open strCnn
    
    Set rstTitles = New ADODB.Recordset
    rstTitles.CursorType = adOpenDynamic
    rstTitles.LockType = adLockPessimistic
    rstTitles.Open "Titles", cnn1, , , adCmdTable
    
    cnn1.BeginTrans

    ' Display connection mode.
    If cnn1.Mode = adModeShareExclusive Then
        MsgBox "Connection mode is exclusive."
    Else
        MsgBox "Connection mode is not exclusive."
    End If

    ' Display isolation level.
    If cnn1.IsolationLevel = adXactIsolated Then
        MsgBox "Transaction is isolated."
    Else
        MsgBox "Transaction is not isolated."
    End If

    ' Change the type of psychology titles.
    Do Until rstTitles.EOF
        If Trim(rstTitles!Type) = "psychology" Then
            rstTitles!Type = "self_help"
            rstTitles.Update
        End If
        rstTitles.MoveNext
    Loop

    ' Print current data in recordset.
    rstTitles.Requery
    Do While Not rstTitles.EOF
        Debug.Print rstTitles!Title & " - " & rstTitles!Type
        rstTitles.MoveNext
    Loop

    ' Restore original data.
    cnn1.RollbackTrans
    rstTitles.Close
    
    cnn1.Close

End Sub