SQL Server returns different record after insert on linked MS Access table

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.

In one case, our users add a new record to a table via a bound form. As soon as the record is saved, a different (much older) record is displayed in its place. Pressing Shift+F9 to force a requery of the form brings back the newly added record (the form is filtered to show only a single record).

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. About 1 in 10 of these history records is created with the wrong Autonumber ID.

Has anyone witnessed this sort of behavior or have any explanation for it?

EDIT: Additional thoughts:

• the backend database is part of a merge replication
• the Access front-end versions are 2000 and 2002 (other versions not tested)
• one post I read suggested Access uses @@IDENTITY behind the scenes to get the newly added record back from SQL Server
• the problem occurs using both the {SQL Server} ODBC driver and the {SQL Server Native Client 10.0} ODBC driver to connect to the backend table
• compatibility level is set to 80 (SQL Server 2000 level compatibility)

EDIT: SQL Profiler Trace results:

I ran SQL Profiler and confirmed that Access is indeed using SELECT @@IDENTITY 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 {SQL Server} ODBC Driver or the {SQL Server Native Client 10.0} ODBC driver.

I should emphasize that Access is using SELECT @@IDENTITY behind the scenes. As far as I know there is no way to force Access to use SCOPE_IDENTITY. Too bad, though, because that seems like it would be the simplest fix.

A bit of looking around (mostly off the link included as "more" by garik), shows that you're stuck with the behavior--it's an Access/SQL Server communication bug. However, there's a workaround described at this link.

It's way too complicated for me to reproduce in detail, and very well explained there, but basicly you save @@IDENTITY to variable at the start trigger, then do a phony #temp insert to spoof the value back to what you want returned at the end.