September 22, 2010 at 6:53 am
Hi all,
We have 2 SQL Servers that update each other using Merge Replication. We then connect to the tables via an ODBC link within Access and this worked without any problems until we upgraded our SQL Server from 2000 to 2008 R2.
Since the upgrade we are having problems within Access. When we come to add a new record, the record returned is different to the one we added. After much research we discovered its down to the triggers within SQL Server (from versions 2005 onwards) where the trigger updates the global variable @@IDENTITY. We've also found out that MS Access uses this variable to return the record that was supposedly last added but since it gets altered as part of a merge trigger it makes Access problematic.
have also read through the usage of SCOPE_IDENTITY and its clear on how it works. the problem, however, is that the triggers are created automatically by SQL Server 2008 when merge replication is set up so i'm unsure of how to modify these triggers to return SCOPE_IDENTITY and not @@IDENTITY.
Also, @@IDENTITY is returned internally within Access and again i'm unsure how to change Access to fetch SCOPE_IDENTITY instead of @@IDENTITY when adding data directly to bound forms.
With this being a very big issue with MS Access and SQL Server Merge Replication triggers I can assume that others would have managed to work around this and come up with a solution.
can anyone help?
June 5, 2020 at 8:42 am
I have change system merge replication triggers wich generate merge triggers to add some bit of code on begening and ending of triggers wich keep right identity value.
This is described in one post of me old few years im microsoft group.
Best regards
January 9, 2021 at 8:49 am
This was removed by the editor as SPAM
January 9, 2021 at 8:49 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply