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.

  • When people talk about corruption with SQL Server, they mean something very specific. I don't see you mentioning error messages associated with corruption, or finding corrupt data with DBCC CHECKDB. Can you clarify what you mean by corruption? – sp_BlitzErik Oct 9 '17 at 0:19
  • I've updated the question with an example. As I said before, I'm quite confident I'm missing something basic (first time I've done Access to SQL Server, and first time doing replication too), I'm just not sure what it is. – Jack Scott Oct 9 '17 at 0:54
  • The title implies that the problem all happens in Access, but the description says that you run "UPDATE queries" and "also have Access itself doing CRUD". So does the corruption only happen from Access or not? Or does the corruption also occur when queries are executed via other clients? – C Perkins Oct 9 '17 at 4:52
  • The UPDATE queries are run from within Access, as pass-through queries. When I run queries from within SSMS, everything appears to work as expected. – Jack Scott Oct 12 '17 at 1:34
up vote 0 down vote accepted

So, it turns out (after hours upon hours of trying to diagnose this) that I did have an issue caused by merge replication being enabled, just not the one I thought I did. For future reference, here's what was happening...

I had a DAO query written in VBA that looked like this:

Set rs = CurrentDb.OpenRecordset("tblExample", dbOpenDynaset, dbSeeChanges)
rs.AddNew

rs.FirstName = "John"
rs.LastName = "Citizen"

rs.Update

rs.Move 0, rs.LastModified
txtPrimaryKey.Value = rs!PrimaryKeyID

rs.Close

The issue was the rs.Move 0, rs.LastModified line, which moves to the most recently updated row (the one just inserted) and then the next line retrieves the new primary key. This had multiple issues:

  • If two users were inserting records at exactly the same time, this would retrieve the same primary key for both users.
  • And of course, the triggers that run after an insert to do merge replication also change things (in other tables) and so this again returns a primary key for what the replication was doing, not what we were doing.

Basically, this code works for single-user databases (which is where it originated) but doesn't work for multi-user scenarios.

The solution (for now) is something along the lines of this, manually retrieving the correct row using all the values just inserted:

txtPrimaryKey.Value = DLookup("PrimaryKeyID", "tblExample", "FirstName=""John"" AND LastName=""Citizen""")

This has other potential issues (if you have two rows with exactly the same data, you'll probably get back the wrong primary key ID) but in this case, for this table, that isn't an issue.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.