IsolationLevel and Mode Properties Example (ADO)


		Contents Index Topic Contents

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