The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting Concurrency Checks section:

Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

The nice thing is that every UPDATE/DELETE statement and bound form will benefit from this addition without having to make any changes within MS Access (aside from re-linking the tables).

I was unaware of this feature until recently. I am considering adding a named rowversion column to every table in SQL Server that I link to from MS Access. Are there any downsides I should be aware of before I do this?

Obviously there will be storage requirements and performance impacts, but I assume these will be negligible. Also, several of these tables are articles in a merge replication scenario; does that make a difference?

up vote 4 down vote accepted

Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a ROWVERSION column to these tables.

One potential issue, though, with merge replication specifically, is if Access grabs a row from one of the servers, then tries to grab a copy of the same row from a different subscriber (not sure if that is possible in your architecture). The reason is that the ROWVERSION values are re-generated at each subscriber, so they won't match the source - this may make it look like the row has changed, when it hasn't. Maybe not possible, or maybe it's ok that such a row always be considered "dirty," but something to keep in mind (and probably test thoroughly).

You might also want to review the merge replication section in this document about schema changes in general.

  • Thanks for the confirmation. The note about the ROWVERSION values differing by subscriber is important to point out. It won't affect my architecture as it currently stands, but it's definitely worth keeping in mind for future development. – mwolfe02 Jul 1 '15 at 14:55

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.