Bug in MS Access Error message: The Data has been changed. "Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record."

Question

We have noticed this bug last October 2018

We have linked a table in MS Access with Azure SQL

when date/time for example is "2019-03-20 01:35:14.8766667" then if 14.8766667 is not Divisible by 2 then a user edit the linked table in MS Access, it will give an Error message: The Data has been changed. "Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record."

Another error message is : Write Conflict "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. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes."

The Result is: Unable to edit the linked table

when date/time for example is "2019-03-20 01:01:12.160" then if 12.160 is Divisible by 2 then a user edit the linked table in MS Access. it does not give any error messages. the results is: we are able to edit the linked table in ms access

We have been using MS Access for a while as well as SQL server. The strange thing is this: the record that shows the locking issue to the user can easily be change directly via the SQL Server back-end. So it must be an issue with how MS Access treats this record

The linked table in MS access doesn’t correctly identify the field value thus when comparing the record before updating the record for this field it reports a change (although there was no change).

I already replicated this exact issue on my test environment and I got the same results

I have used all other MS Access Channels

Office Insider, Monthly Channel, Monthly Channel (Targeted) , Semi-annual Channel (Targeted) ,Semi-annual Channel 

I have used other Virtual machines and other networks

But the Issue is still the same

Any idea on this issue?

Answer 1

Ok, several things to check + test that may well help this issue.

If you have a bit column in sql server, make sure you set the default to 0, and not null. This is a long time known error to cause the write conflict. (Your example does not suggest this issue but when we grasping at straws to troubleshoot, then we want to make sure this issue is not part of the problem).

To be fair, the newer native drivers actually now return 0 (false) for those nulls – so we don’t see this problem much anymore.

So, I do not think the above is your issue – but simply you should be aware that all bit columns REALLY need a default value set on the sql server side.

Now, for near forever, the recommend approach when using Access with sql server is to include for ALL tables what is called a row version column (a timestamp column).

So your simple fix here is to add a rowversion column to any table you plan to use/link to from Access.

This rowversion column is NOT to be confused with a date/time column.

If you don’t do the above, then Access is forced to do a column by column compare to determine a record change. (and it sends a VERY ugly query to the server to check for changes). If your table say has 120 columns, then Access will query all 120 columns for this check. And what occurs is some “rounding” errors when you use the timedate2(7) new column time - so access fails and thinks the record has changed.

If you include the timestamp column in all your tables, then access ONLY compares and looks at the ONE timestamp column to determine if the sql record has changed.

In fact, the migration wizard(s) be it access or the SSMA tools ALWAYS give you an option to add a timestamp (rowverison) column during a migration, and this advice has been around for at least 20 years – even before the new timedate2(7) columns that appeared for SQL server.

So, adding a timestamp column, and re-linking your tables will fix this issue.

So, you can reduce some (in fact a lot) of network chatter, and save access some pain, but you also fix your problem if you do this.

Add a rowversion column, and don't forget to re-link your tables, this should fix this issue for you.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 2

The ODBC driver, as is, can't translate these nanosecond values reliably for those date values. The maximum interval is:

1899-12-20 to 1900-01-09

For microseconds it is:

1872-08-13 to 1927-05-18

Milliseconds, however, can be covered for the entire range of the Date values, though by default, they will be displayed anywhere in Access rounded to the second.

As you probably aren't interested in the nanosecond fractions stored, you should ask yourself from where they origin? It can't be in Access unless you perform some weird numeric operations on these values.

The simple solution could be to run an update query in SQL Server that rounds the values to the second. If the values origin from outside Access, you could run this query on a regular basis or, of course, see if you can modify the source to provide data holding no second fractions.

Answer 3

I think I know what you mean. The same problem exists with system-versioned tables, a fairly new feature of SQL Server. The date precision of SQL Server is greater than what Access can handle. The Access team is aware of the situation but as far as I know there is no easy solution and they are still looking at options. I will relay your message.

Answer 4

Michael Lewis

I recently updated Access to 365 and MySQL from 5.6 to 8.0 on a new machine.

Since, I have been having "fun" with the MySQL ODBC 8.0  ">1024" characters in a field, bug - but that's another (awful) matter.

I tried today for the first time to change the value of a Long integer / Int field and got the problem described above.

I had an MySQL field, I called DateTIme - but noticed that I had inadvertently defined it in MySQL as datetime rather than TimeStamp.

On re linking the table the table, I was able to change the field without the message.

The joys of upgrading!

I can't find any "rowversion" type in MySQL - but a timestamp field, correctly defined, does the job.

Answer 5

Thank you so much for your help Albert, This RowVersion resolves the issue with MS Access.

:D