ADO Connection Object IsolationLevel Property

The level of transaction isolation for a Connection object. Transactions are not currently supported on UNIX.

IsolationLevel Property Return Values

Sets or returns one of the following IsolationLevelEnum values:

Constant	Description
adXactUnspecified	The provider is using a different IsolationLevel than specified, but the level cannot be determined.
adXactChaos	You cannot overwrite pending changes from more highly isolated transactions.
adXactBrowse	You can view uncommitted changes from one transaction in other transactions.
adXactReadUncommitted	Same as adXactBrowse.
adXactCursorStability	Default. You can view changes in other transactions only after they have been committed.
adXactReadCommitted	Same as adXactCursorStability.
adXactRepeatableRead	You cannot see changes in other transactions, but requerying can bring new Recordset objects.
adXactIsolated	Transactions are conducted in isolation of other transactions.
adXactSerializable	Same as adXactIsolated.

IsolationLevel Property Remarks

Use the IsolationLevel property to set the isolation level of a Connection object. The IsolationLevel property is read/write. The setting does not take effect until the next time you call the BeginTrans method. If the level of isolation you request is unavailable, the provider may return the next greater level of isolation.

IsolationLevel Property Example

This example uses the ADO Connection Object 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