title: Get ID of Last Inserted Record - Access DAO, ODBC, SQL Server 2008 Identity Field
body: <p>It's a very common question but I'm having trouble getting the ID of the last inserted record. I'm using DAO with ODBC linked tables to duplicate a record and it's child records. My tables are in SQL Server 2008 and have Identity fields for ID fields.</p>
  
  <p>Here's what I've tried so far. My first bit of code here results in error 3167, Record is Deleted. If I do a debug.Print the recordset actually contains 3 records.</p>
  
  <pre><code>Dim r as DAO.Recordset, db as DAO.Database
  Set db = CurrentDb
  Set r = db.OpenRecordset("SELECT TOP 2 * FROM item ORDER BY DateTimeModified DESC", dbOpenDynaset, dbSeeChanges)
  r.AddNew
  'Set field values here
  r.Update 'Completes without error
  r.Bookmark = r.LastModified
  Debug.Print r("ItemID") 'Error 3167, Record is deleted
  </code></pre>
  
  <p>Here's the next thing I tried:</p>
  
  <pre><code>Debug.Print db.OpenRecordset("SELECT @@identity FROM item")(0)
  </code></pre>
  
  <p>This last one completes without any problem but the value returned is incorrect. Where the actual new ItemID is 321 this returns the value 614. The value it is returning does appear to be incremental (it changes as I keep testing this) but it does not appear to relate at all to my table. There is no field with the value 614. I've double checked to make sure I'm looking up the correct table.</p>
  
  <p>I know I could use something like DLookup or DMax but I don't think that would be considered bullet proof in a multi-user environment.</p>
  
  <p>I suppose I could use a Stored Procedure with ADO to get around this problem. I'm wondering if that is my only option?</p>
  
  <p><strong>Edit1:</strong><br>
  I'm now using the following code and it is doing what I need/want it to. I suspect this is basically the same as using DMax.  </p>
  
  <pre><code>Dim r as DAO.Recordset, db as DAO.Database
  Set db = CurrentDb
  Set r = db.OpenRecordset("SELECT TOP 1 * FROM item ORDER BY ItemID DESC", dbOpenDynaset, dbSeeChanges)
  r.AddNew
  'Set field values here
  r.Update
  r.Requery
  r.MoveFirst
  Debug.Print r("ItemID")
  </code></pre>