IsolationLevel and Mode Properties Example

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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"

	' 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