MS Access Write Conflict - SQL Server - Me = Dirty

Question

I'm getting the error message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made...

I know this is a common question and I've spent hours researching and testing but to no avail. A few notes:

1. There are no bit fields anywhere in my database
2. All tables have a primary key, data type = identity
3. All tables have a create/modified timestamp trigger on updates and insert

I'm fairly certain that the problem has to do when the form (and multiple subforms) are creating the identity fields and/or the timestamp triggers. Specifically, I only get this error on the "Individual Fish" table when I go back to edit an old 'fish' (as shown on the screenshot). If I just tab through the form and don't make any edits, it works fine. But if I need to edit anything on a previous 'fish' - after the identity / trigger fires - then it gives me the error.

I've gone through and added If Me.Dirty Then Me.Dirty = False End If to every form for the following events: On Current, On Load, On Click, After Update, Before Update, Before Insert, On Dirty.

I also added DoCmd.RunCommand acCmdSaveRecord to On Deactivate. I will admit that I am not great at VBA, so there could be something silly I did here. Code attached. I've also messed around with Record Locks = Edited Record.

So far nothing seems to work. Please let me know if you think I'm missing something. Also, if you have any random comments or suggestions about my database design or anything else, I always welcome feedback.

Thanks!

UPDATE:

Albert's answers got me to the right place. The short version is to add a rowversion (aka timestamp) field to all tables. This was mentioned on several other posts, but i didn't realize the [awfully named] "timestamp" didn't actually have to do with date or time. Thanks for the help!

]3

Answer 1

Ok, lots of things here to check. First of all, placing a me.Dirty = false in on-current, or events like before update will cause the "same" event to try and trigger again. You really (but really really) don't want to do this. (so wild random tossing in of me.dirty in those events will only make this issue much worse and often cause the very same event to trigger again.

next up:

All tables have a create/modified timestamp trigger on updates and insert

Ok, now the above is confusing. Do you have an actual trigger - as that is a separate issue and will often trigger the record been modified by someone else.

Also when we speak of a timestamp column, do keep in mind that such columns have ZERO ZERO ZERO to do with datetime. Over the years Microsoft has attempted to "change" the name used. The correct name is ROWVERSION, and this column is NOT a datetime data type column, but is called timestamp. Do NOT in ANY WAY confuse this rowversion system/column with that of a datetime column.

So, the assumptions are: You have a timestamp column - this is of data type timestamp. This column is NOT touched by your code or trigger in ANY WAY. This column is NOT of datetime, nor of datetime2, but is of data type timestamp.

If you don't have a actual timestamp column here (it does not need to be on the form), then you will get constant "dirty" warnings. You also get this with any real data type columns - especially if they are set by server code. (access will round differnt).

Bottom line: You need a actual rowversion column (of type timestamp) in that table. Behind the scenes if access does NOT find this column, then it will do a column by column compare, and without question with a trigger to set some LastUpdated column with GETDATE() on the server side trigger, then this will cause nothing but problems. Introduction of a timestamp column will STOP access from doing the column by column compare after a update, and it will ONLY look at the timestamp column. So, your trigger can now update the LastUpdated, and the timestamp column should not change from access points of view.

So, you need to be sure:

A PK column is seen by access - all sql tables need a PK.

All tables should have a rowversion column.

If you do add a timestamp (rowverison) column to the problem table, then make sure you re-link on the access client side. In fact after ANY table change or modifications server side, then you should re-link the client side.

I would remove any stray me.Dirty = False code in that form.

You can place a "save" button on the form if you wish, and simply have it go

if me.dirty = true then me.Dirty = False

Edit

With the above setup, you should be able to re-introduce your server side trigger that sets the LastUpdated. However, you not want any code in the form that "touches" or uses that column. You can however should be able to drop in that LastUpdated column into the form and see it update after you save.

Answer 2

Bottom line as I have run across this error on an upgrade of SQL Server to 2016, due not assume "timestamp" is of data type "datetime". It is not. The data type that Access requires is of type "timestamp". Add a column with that data type to any table that is editable through Access and that will clear the "Write conflict with grayed out save button" message.