ADO Record Locking

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

---