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.

'BeginIsolationLevelVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub IsolationLevelX()

   Dim Cnxn As ADODB.Connection
   Dim rstTitles As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQLTitles As String

   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Mode = adModeShareExclusive
   Cnxn.IsolationLevel = adXactIsolated
   Cnxn.Open strCnxn
   
    ' open Titles table
   Set rstTitles = New ADODB.Recordset
   strSQLTitles = "titles"
   rstTitles.Open strSQLTitles, Cnxn, adOpenDynamic, adLockPessimistic, adCmdTable
   
   Cnxn.BeginTrans

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

   ' Display isolation level
   If Cnxn.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
   Cnxn.RollbackTrans
   rstTitles.Close
   Cnxn.Close
   Set rstTitles = Nothing
   Set Cnxn = Nothing

End Sub
'EndIsolationLevelVB

IsolationLevel and Mode Properties Example (VB)

See Also