title: SQL Server returns different record after insert on linked MS Access table
body: <p>We recently upgraded our backend database from SQL Server 2000 to SQL Server 2008.  Since the switch we've had intermittent (read: impossible to consistently reproduce) and strange problems, but they all seem to be related somehow.</p>
  
  <p>In one case, our users add a new record to a table via a bound form.  <strong>As soon as the record is saved, a different (much older) record is displayed in its place.</strong>  Pressing <kbd>Shift</kbd>+<kbd>F9</kbd> to force a requery of the form brings back the newly added record (the form is filtered to show only a single record).</p>
  
  <p>We have managed to isolate a specific instance of the problem based on logging that occurs on a different form.  In the BeforeUpdate event of the form a timestamp is correctly filled in on the record being inserted.  In the AfterUpdate event of the same form a history record is created in another table that includes the Autonumber ID of the first table.  <strong>About 1 in 10 of these history records is created with the wrong Autonumber ID.</strong></p>
  
  <p>Has anyone witnessed this sort of behavior or have any explanation for it?</p>
  
  <p><strong>EDIT:</strong> Additional thoughts:</p>
  
  <ul>
  <li>the backend database is part of a merge replication</li>
  <li>the Access front-end versions are 2000 and 2002 (other versions not tested)</li>
  <li>one post I read suggested Access uses <code>@@IDENTITY</code> behind the scenes to get the newly added record back from SQL Server</li>
  <li>the problem occurs using both the <code>{SQL Server}</code> ODBC driver and the <code>{SQL Server Native Client 10.0}</code> ODBC driver to connect to the backend table</li>
  <li>compatibility level is set to 80 (SQL Server 2000 level compatibility)</li>
  </ul>
  
  <p><strong>EDIT:</strong> SQL Profiler Trace results:</p>
  
  <p>I ran SQL Profiler and confirmed that Access is indeed using <code>SELECT @@IDENTITY</code> behind the scenes to return the newly inserted record.  I confirmed this is happening with MS Access 2000, 2002 (XP), and 2007 front-ends.  It is also happening whether the tables are linked using the <code>{SQL Server}</code> ODBC Driver or the <code>{SQL Server Native Client 10.0}</code> ODBC driver.</p>
  
  <p>I should emphasize that Access is using <code>SELECT @@IDENTITY</code> <em>behind the scenes</em>.  As far as I know there is no way to force Access to use <code>SCOPE_IDENTITY</code>.  Too bad, though, because that seems like it would be the simplest fix.</p>