I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (from SQL Server Standard on a proper server as the publisher to SQL Server Express on a laptop as a subscriber using Merge Replication) we start getting weird database corruption that I can't explain.
What happens is that we have UPDATE queries which run against several tables in the database, and we also have Access itself doing CRUD operations against multiple tables as well (this corruption is not limited to one table). The result we see is that randomly (about 1 in 10 operations) a row other than the one we wanted to update is being updated, which overwrites data we didn't want to overwrite.
The replication, when set up, runs on demand on the laptop as a pull merge, and the corruption happens regardless of whether replication is performed. It only has to be enabled. No corruption seems to occur when replication isn't enabled.
I'm not accusing Microsoft of any fault here - it's entirely probable I've just forgotten to tick some box to prevent this. I'm just not sure what it is that I need to look for.
Edit: What I mean by corruption is this: Let's say I have rows:
ID | FirstName | LastName -------------------------- 1 | John | Smith 2 | Emma | Citizen 3 | Bob | May
I then run something along the lines of:
UPDATE Table SET FirstName = "Test" WHERE ID = 1
And after that happens I end up with this:
ID | FirstName | LastName -------------------------- 1 | Test | Smith 2 | Test | Citizen 3 | Bob | May
There are no error messages in any of the system tables dealing with replication. The only change in the schema is that when replication is enabled it creates the rowguid column.