-
Has anyone done any work with ADO and Record locking?
I am trying to open a record set and lock the data for read only to other users while the current user is working with the data. I have tried chaning the mode setting on the Connection string but cannot get the locking to happen.
Any suggestions.
-
I'm sure paulw will tell me if i'm wrong, but im pretty sure you will need to use the Mode property in conjunction with the IsolationLevel property to achieve the locking you need, you then need to use transaction based statements (BeginTrans e.t.c) to make sure that all your SQL is encapsulated in these to tell the server when to hold locks on the appropriate data...
N.B. you will also need to ensure that your CursorLocation is set to serverside to support these features...
heres an example from the data access SDK:
Code:
'BeginIsolationLevelVB
Public Sub IsolationLevelX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
' Assign connection string to variable.
strCnn = "Provider=sqloledb;" & _
"Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
' 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
'EndIsolationLevelVB