Feedback by UserVoice

I suggest you ...

Use scope_identity() to refresh linked SQL-Server tables or views

After inserting a row into a linked SQL-Server-table or view Access is refreshing the row. For these query on the new row, Access builds a where-condition which compares all fields with the known values.

If a inserted value gets changed in the meantime e. g. by a Trigger, the where-condition fails and the returned resultset is empfy. Access is showing then the text "#deleted" in the row.

Before SQL-Server 2008 this was probably the only way, to get the new identity of the new row.

Since SQL-Server 2008 exists the function scope_identity() which returns the new identity of a row in the actual scope.

Please change use the scope_identity-function to get the new identity.

4 votes
Vote
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
You have left! (?) (thinking…)
Paletti shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • John commented  ·   ·  Flag as inappropriate

    I noticed this recently when I tried to sync an access table to an azure database. There needs to be an easy way to get the new record ID from a record add into SQL when triggers are firing. My impression is that this may be an ODBC problem but Access needs to be able to figure the right key and supposedly SCOPE_IDENTITY() and NOT @@IDENTITY can work if we can retrieve that value.

    Thanks for your attention.

Feedback and Knowledge Base