title: Run-time error 3197 when attempting to update record via DAO recordset
body: <p>I've got a Microsoft Access 2016 application, currently running with a SQL Server 2019 backend. I'm having issues attempting to use a recordset.update to save changes to a particular record in my products table. I'm entirely self-taught and have been coding with vba for around 18 months, came across this issue with bit fields in the past, but I can't see what's causing it this time.</p>
  <p>The error:</p>
  <p><strong>Error 3197: the microsoft access database engine stopped the process because you and another user are attempting to change the same data at the same time</strong></p>
  <p>Things I have tried:</p>
  <ol>
  <li><p>The table has a primary key</p>
  </li>
  <li><p>Most records in this table allow me to edit and update the changes (there's a random selection that will not work)</p>
  </li>
  <li><p>All bit fields have default values set to 0</p>
  </li>
  <li><p>I've tried only updating 1 of the many fields to see if that effects the outcome, it doesn't, no matter if I'm editing a integer field only, or multiple mixed types, it still occurs</p>
  </li>
  <li><p>I can directly change the values of any field in this table using a SQL Query (pass through or via Access query designer)</p>
  </li>
  <li><p>I cannot change any value on these records (others are fine) via the table directly (using ms access table view, I get the Drop Changes / Save to clipboard dialog box)</p>
  </li>
  <li><p>Although this does happen when using this particular function via a form, even when running the code directly via the immediate window in the vba editor, I get the same error. But I ensured that the record locking used by the form was no locks.</p>
  </li>
  <li><p>The default open mode is set to shared (for the db, via Options -&gt; Client Settings)</p>
  </li>
  <li><p>The default record locking is no locks (for the db, via Options -&gt; Client Settings)</p>
  </li>
  <li><p>Currently using Row-Level Locking (for the db, via Options -&gt; Client Settings)</p>
  </li>
  </ol>
  <p>For reference, the code is simple and straightforward enough:</p>
  <pre><code>Dim dbs As DAO.Database
  Dim productRS As DAO.Recordset
  
  Set dbs = CurrentDb
  Set productRS = dbs.OpenRecordset(&quot;Select * From tblProducts WHERE ProductID = &quot; &amp; ProductID, dbOpenDynaset, dbSeeChanges)
  
  With productRS
  .Edit
  !Name = someName
  .Update
  End With
  
  rs.close
  dbs.close
  
  set rs = nothing
  set dbs = nothing
  </code></pre>
  <p>I also have a RecordTimestamp field (datetime2(7)) that uses GetDate() as a default value for the record, and it's been set this way from the beginning of the project which leads me to think it's not an issue with this field. But I don't fully understand how exactly sqlserver and access remember/determine if a record is locked/being updated or if there's a way I can clear all these pending/non-existant locks.</p>
  <p>Thanks in advance if anyone can think of something that would be causing this issue.</p>