Linked Access Db "Record Has Been Changed by Another User"

Linked Access DB record has been changed by another user

Take a look at your linked table in SQL Server 2000. Does it have a field containing the bit datatype? Access will give you this error message in a linked table scenario if you have a bit field which does not have a default value.

It might not be what's wrong in your case, but I've experienced the same in an Access 2007 database and tracked the problem to a bit field with no default value.

MS Access - This Record Has Been Changed By Another User Since You Started Editing It

2. Added a datetime column to the SQL database that gets updated with a trigger ON UPDATE

This could actually be the cause of your problem - Access and Sql Server modifying the same row at the same time is causing the write conflict you see.

I'm afraid you have misinterpreted the valid advice you found.

What you want is a ROWVERSION aka TIMESTAMP column in your Sql Server table. That helps Access figuring out if a row has changed and solve write conflicts. It is fully automatic and needs no trigger.

More details (Albert always provides lots of details :p ) here:

MS Access Write Conflict - SQL Server - Me = Dirty

If this doesn't help: check if you have any other triggers, and post your VBA code for filter / edit / save.

Record has been changed error if data is changed by VBA

Yes, the issue is due to linked ODBC tables. Plus floating point number columns, which can cause problems when Access checks whether your changes in the bound form (be it by VBA or manually) conflict with the previous version of the saved record.

The solution should be to add a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to your table.

From here:

ALTER TABLE myTable
ADD COLUMN updated_at 
  TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP;

See these questions:

Write Conflict messages suddenly start happening in ODBC linked tables

Does MySQL have an equivalent of SQL Server rowversion?

For tables linked from SQL Server, adding a ROWVERSION column definitely fixes the issue. For MySql (and its ODBC driver) it should work, and it did work here.

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.

The data has been changed error when stepping from main form into sub form

After much trial and error I solved the issue. In the enter event handler for the sub form control on the main form, I requeried the sub form itself.

eg On the main form:

Private Sub Subform1_Enter()
    Me.Subform1.Form.Requery
End Sub

I don't know why this works, only that it does.

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 when porting backend from MS SQL to MYSQL

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

How to get the record details which has been changed in Access database

I had a similar problem a couple of years back with an MSSQL DB and I seem to recall that both ACCESS (MDB) and SQL (MDF) Databases DON't inherantly support this. You, if I am not mistaken, will have to adopt the principles:

1) Transactional Data should NEVER be adited, only reference historical records with new records containing the changed values. I took it from three angles :

• I had a column called PrecursorRecId which held the record_id of the
  most recent record the update related to.
• Another column held and Context_ID under which all related records
  were linked by a common value which was the key Id column of
  AuditTxnContext table.
• Record's TxnDate (pretty logical)

2) Master Data records Also were not altered but each record had an EffectiveFromDate and and EffectiveToDate where the records with a NULL EffectiveToDate were considered current. Depending on the sensiitivity of the table, creation of MaterData records was accompanied by Audit Entry records. Again, there is a RecordCreatedDateTime which does not automatically match the EffectiveFromDate stamp.

This may help you going forward but there's not much to assist with your existing data. I also don't have much idea of what it is you are working on so my input is fairly generic. I hope it helps any way. I would welcome anyone's input if I am in error, this is just how we approached our challenge.

Tell me a bit more if you are still dealing with it. If not, maybe close your question.

Cheers

Mac

---

Related Topics