title: Is there a way to lock record in Access database
body: <p>I need to find a way to get unique "Verification" from MS Access database in multi-users environment. Here's my table example:</p>
  
  <p><a href="https://i.stack.imgur.com/Zzykj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Zzykj.png" alt="enter image description here"></a></p>
  
  <p>Field Verification is already prefilled and will be never changed, mID, and getDate are added/deleted by user (using VBA script).<br>
  The rule for VBA script is:<br>
  1. Find first free row of mID,<br>
  2. Fill it with current user Name, and current Date,<br>
  3. Return corresponding Verification field</p>
  
  <p>Seems easy to do, here's code I've developed:</p>
  
  <pre><code>Function access() As String
  
      Dim con As ADODB.Connection
      Dim Records As ADODB.Recordset
      Dim record As ADODB.record
  
      Set con = New ADODB.Connection
      Set Records = New ADODB.Recordset
      Set record = New record
  
      con.ConnectionString = sDBPath
      con.Open
  
      With Records
          .ActiveConnection = con
          .Source = "tblVerificationNr"
          .LockType = adLockOptimistic
          .CursorType = adOpenDynamic
          .Open
  
      Do Until .EOF
          If IsNull(.Fields("mID")) Then
              access = .Fields(1)
              .Fields("mID") = "asdf"
              .Fields("getDate") = Now
              .Update
              Exit Do
          End If
          .MoveNext
      Loop
  
      End With
  
  End Function
  </code></pre>
  
  <p>Code works well for 1 user, but when multiple users starts querying it at (almost) same time, then they receive the same Verification number.
  (If User2 sends request 0,1 sec later than User1, then they'll find the same row - Verification = 3006. It will result that first User1 will put his mID and getDate AND will receive Verification = 3006, but 0.1 sec later User2 will overwrite this data and receive the same verification number).  </p>
  
  <p>What would be a work-around for this one? How to ensure Verification uniqueness? Is it possible to "lock" found record by user1, so user2 would not be able to find this one?</p>
  
  <p>Tried it with SQL statements as well:</p>
  
  <pre><code>sSelectSQL = "SELECT TOP 1 tblVerificationNr.VerificationNumber, tblVerificationNr.Mid, tblVerificationNr.getDate FROM tblVerificationNr WHERE (((tblVerificationNr.[mID]) = '" &amp; UserName() &amp; "')) ORDER BY tblVerificationNr.getDate DESC, tblVerificationNr.ID"
  sUpdateSQL = "UPDATE (SELECT TOP 1 tblVerificationNr.VerificationNumber, tblVerificationNr.Mid, tblVerificationNr.getDate FROM tblVerificationNr WHERE (((IsNull([tblVerificationNr].[mID]))&lt;&gt;False)) ORDER BY tblVerificationNr.VerificationNumber)  AS a SET a.mID = '" &amp; UserName() &amp; "', getDate = '" &amp; Now() &amp; "'"
  </code></pre>
  
  <p>and the same issue here.</p>