Editing Record Issues in Access/SQL (Write Conflict)

Editing Record issues in Access / SQL (Write Conflict)

Possible problems:

1 Concurrent edits

A reason might be that the record in question has been opened in a form that you are editing. If you change the record programmatically during your editing session and then try to close the form (and thus try to save the record), access says that the record has been changed by someone else (of course it's you, but Access doesn't know).

Save the form before changing the record programmatically.

In the form:

'This saves the form's current record
Me.Dirty = False

'Now, make changes to the record programmatically

2 Missing primary key or timestamp

Make sure the SQL-Server table has a primary key as well as a timestamp (= rowversion) column.

The timestamp column helps Access to determine if the record has been edited since it was last selected. Access does this by inspecting all fields, if no timestamp is available. Maybe this does not work well with null entries if there is no timestamp column (see 3 Null bits issue).

The timestamp actually stores a row version number and not a time.

Don't forget to refresh the table link in access after adding a timestamp column, otherwise Access won't see it. (Note: Microsoft's Upsizing Wizard creates timestamp columns when converting Access tables to SQL-Server tables.)

3 Null bits issue

According to @AlbertD.Kallal this could be a null bits issue described here: KB280730 (last snapshot on WayBackMachine, the original article was deleted). If you are using bit fields, set their default value to 0 and replace any NULLs entered before by 0. I usually use a BIT DEFAULT 0 NOT NULL for Boolean fields as it most closely matches the idea of a Boolean.

The KB article says to use an *.adp instead of a *.mdb; however, Microsoft discontinued the support for Access Data Projects (ADP) in Access 2013.

MS Access Write Conflict - SQL Server - Me = Dirty

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.

suppress write conflict message in Access VBA

(I guess I should put my comments in a post, since I'm actually answering the question)

The reason you're getting the write conflict message is because you've created a write conflict by editing the record via the form and via the stored procedure. To avoid the error, simply save the record in the form before executing the stored procedure. From your code snippet posted above, it should be:

Me.Dirty = False
cmd.Execute , , adCmdStoredProc

This is a common Access error, and it's caused by trying to edit the data through a bound form and through direct SQL updates. You need to save the edit buffer of the form before updating the same record via SQL.

In other words, you should be grateful that the error message is happening, because otherwise, you'd lose one or the other of the changes.

I question the design, though. If you've got the record open in a bound form, then make the edits to the data loaded in the form, rather than running SQL to update it. I'm not sure why you need a stored procedure to make changes to a record you've already edited in a form -- it sounds like a design error to me (even if the solution is quite simple).

MS Access Write Conflict when porting backend from MS SQL to MYSQL

"return matching rows instead of affected" needs to be activated.

Editing Record Issues in Access/SQL (Write Conflict)