sql CLR trigger causing havoc in Access linked table

Have been running an Access front end (mdb)
on a SQL Server back end for several years.
Will soon be migrating to SQL2005. Have
detected a showstopper of a problem.

Some background:
One reason to move to SQL05 is the use of CLR
for a generic audit trigger (based on code
made available by others). Trigger works
well. For an example, see
http://www.sqlservercentral.com/columnists/dziffer/creatingagenericaud...riggerw

Here’s the problem: when Access inserts a
new row in a linked, audited table, SQL
should return the ID (identity) of the new
record, and then Access can refresh that row
– the ID field’s value and field value with
defaults should be available to the Access
table. However, when the CLR trigger fires,
SQL returns the ID of the row from the audit
table, not the table where the original
insert occurred. So, if I am inserted my
200th record into table1, and this creates a
49th record in my audit table, AND table1
contains a record where ID=49, then the
Access table will change its current record
to row where ID=49. The new record still
gets inserted correctly; the trigger fires
correctly; the problem is solely how Access
represents the current/new record. I
probably don’t need to explain the danger of
having the current record flip on a user
without notice.

Anyway, am assuming this might be related to
difference between @@identity and
scope_identity(), but am not able to tell.
What’s curiouser, if the id returned by the
audit table is not found in table1, then
Access behaves as it should.

Any advice on how to proceed? Or do I need
to backtrack, and implement a purely SQL
solution? Is anyone from Microsoft out there
who understands why is occurring?

Lew