Share via

MS Access Merge Subscriber not able to upload changes when the column order is non-sequential for article

  • Article

If you have a merge publication with many articles and you may found that in one of the articles, you are not able to upload changes made to two columns from MS Access subscriber to publisher. This table is not part of any subset or join filter.

The issue occurs because the column order of the published article is not sequential. sysmergearticles shows missing_cols value being set (i.e. not 0x00). MS Access subscriber fails to understand this and is unable to upload changes. SQL Server subscribers work fine in this case.

The problem also reproduces with MS Access 2003 and MS Access 2007 (office 12) subscribers. Here is how you can reproduce it quickly:
================
a. Create a new table with two columns say, col1 and col2
b. Add a third column say col3 and col4
c. Copy the contents of col2 into col4
d. alter the table to drop col2
e. Alter the table to add col2 with new datatype again
f. Drop col4. Make sure that the colorder for this table is not sequential
g. Create a merge publication on this article
h. Create MS Access subscriber (.mdb) for this publication and apply the snapshot
i. Make changes to col2 column value on the publisher first then sync. Now make a change at subscriber and run merge synchronization. You will see that the changes to col2 are not uploaded from subscriber to publisher.

The root cause of this issue is the MS Access Replication Provider i.e. msrpjt40.dll is not able to unsderstand the non-sequential column order here. You
can confirm the non-sequential column order by running the following query on publisher:
select o.id, o.name, c.name, c.colorder from syscolumns c join sysobjects o on
c.id=o.id where o.id = object_id('<your article name')

Resolution:
This issue does not look like having a resolution here. However, you can take this workaround approach

a. Script out the table and export the data out.
b. Remove this article from publication using sp_dropmergearticle
c. Create the table again using the script. Import the data back. Confirm that the column order is sequential.
d. Add the article back to the publication using sp_addmergearticle
e. Apply the snapshot on the subscribers.

Comments