Identity columns in SQL Server tables linked to Access

[Kipp Woodard]

When Access inserts a record into a SQL Server linked table it should use
scope_identity(), instead of @@Identity to get the identtity of the inserted
record.

This issue really burned me bad when I upsized to SQL Server. My tables
have Identiy columns. They also have triggers on them for insert. The
insert trigger writes a copy of the new record to an audit table, which has
it's own identity column.

The impact was bad. When a row was added on a form in my app, Access got
the identity of the new audit table row (instead of the identity of the new
target table record) and loaded the record from the target table that had the
identity number of the new audit table record. Then, since this was being
done in a macro, it resulted in the wrong record being modified. This was
catestrophic until I figured out what was going on and dropped the identities
from the audit tables.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=9f9904e7-3a66-4d43-a823-e8acfe0ff183&dg=microsoft.public.access.sqlupsizing

[Alex Dybenko]

Hi,
you can solve this but modifying trigger code. At the trigger beginning you
store identity in a variable and at the end restore it by inserting into
temporary table.
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Kipp Woodard
When Access inserts a record into a SQL Server linked table it should use
record.
This issue really burned me bad when I upsized to SQL Server. My tables
have Identiy columns. They also have triggers on them for insert. The
insert trigger writes a copy of the new record to an audit table, which has
it's own identity column.
The impact was bad. When a row was added on a form in my app, Access got
the identity of the new audit table row (instead of the identity of the new
target table record) and loaded the record from the target table that had the
identity number of the new audit table record. Then, since this was being
done in a macro, it resulted in the wrong record being modified. This was
catestrophic until I figured out what was going on and dropped the identities
from the audit tables.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=9f9904e7-3a66-4d43-a823-e8acfe0ff183&dg=microsoft.public.access.sqlupsizing