title: How to Lock Only the Current ADODB.Recordset Instead of the Whole Page (or Table) in VBA/Access
body: <p>I want to lock just the current ADODB recordset (but not the page or the entire table) of a multi-user system developed in VBA/Access so that any other user that opens the same recordset cannot edit one recordset position already in edit mode in some other user computer. I want to avoid 2 o more users editing the same record position (eg: more than one user editing the registration of the same employee).</p>
  
  <p>I tested the folowing code, unsuccessfully:</p>
  
  <pre><code>Private Sub Form_Load()
      Dim dbcon As ADODB.Connection
      Dim recrdst As ADODB.Recordset
  
      Set recrdst = New ADODB.Recordset
  
      recrdst.CursorType = adOpenKeyset
      recrdst.LockType = adLockPessimistic
  
      Set dbcon = New ADODB.Connection
  
      dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
                 "Data Source=C:\Família\Alex Borges\Companies\Imobiliária Halex Tiago\Estudos\Sistema Multi-Usuário\Servidor.mdb;"
  
      recrdst.Open "SELECT * FROM tblDependentes", dbcon
  
      Set Me.Recordset = recrdst
  
      Set recrdst = Nothing
      Set dbcon = Nothing
  End Sub
  </code></pre>
  
  <p>I also tested the folowing code:</p>
  
  <pre><code>Private Sub Form_Load()
      Dim dbcon As ADODB.Connection
      Dim recrdst As ADODB.Recordset
  
      Set dbcon = New ADODB.Connection
      dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
                 "Data Source=C:\Família\Alex Borges\Companies\Imobiliária Halex Tiago\Estudos\Sistema Multi-Usuário\Servidor.mdb;"
  
      Set recrdst = New ADODB.Recordset
      recrdst.ActiveConnection = dbcon
      recrdst.CursorType = adOpenKeyset
      recrdst.LockType = adLockPessimistic
      recrdst.CursorLocation = adUseServer
  
      recrdst.Open "SELECT * FROM tblDependentes", dbcon
  
      Set Me.Recordset = recrdst
  
      Set recrdst = Nothing
      Set dbcon = Nothing
  End Sub
  </code></pre>
  
  <p>my Access version is 2007. on Access/Options/Advanced, I changed the default record locking to "edited record".</p>
  
  <p>thanks in advance.</p>