IsolationLevel and Mode Properties Example (VB)

Microsoft ActiveX Data Objects (ADO)

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