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.
Paletti
shared this idea
2 comments
-
Anders Ebro (TheSmileyCoder)
commented
This is an ODBC driver issue, not limited to Access, in other words it is the ODBC driver using Identity instead of Scope_identity.
-
John
commented
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.