IsolationLevel, 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
	
	With rstTitles
		.MoveFirst
		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 .EOF
			If Trim(!Type) = "psychology" Then
				!Type = "self_help"
			.Update
			End If
			.MoveNext
		Loop

		' Print current data in recordset.
		.Requery
		.MoveFirst
		Do While Not .EOF
			Debug.Print !Title & " - " & !Type
			.MoveNext
		Loop
		
		' Restore original data.
		cnn1.RollbackTrans
		.Close
	End With
	
	cnn1.Close

End Sub

© 1997 Microsoft Corporation. All rights reserved. Terms of Use.