ADO Record Locking

[FMCAR10]

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.

[crispin]

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:

'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