none
ODBC linked MySQL Backend MS Access Frontend record changed by another user error on update RRS feed

  • Question

  • Hi there,

    I have an MS Access 2003 standalone database that I migrated to a MySQL backend  with an MS Access 2013 front end.

    I have a development and production setup which were identical and both working for several months.

    Lately, the production front end will not allow users to update some data in some records in one table.

    Whenever the update a record in a form a dialog box pops up and says:

    "This record has been changed by another users since you started editing it." and prompts to copy to clipboard or discard.

    I can close all of the forms, open the linked table, try to update the same record and get the same error. However, this only happens with some records, not the majority of 17,000 records.

    The development version works just fine and it only has two more records that the production version. All the tables were basically pre-created with sufficient records to handle all data update needs.

    There is no referential integrity built into the database.

    I have researched and found the cause of this error to be either:

    1) a bit field being not getting the right value

    2) No autonumber field on the migrated table

    There are no bit fields or other fields getting an invalid default value.

    When I migrated the MS Access 2003 tables to MySQL, I created the tables with SQL and the type on the Primary Key is Autoincrement.

    I have read that maybe I also need to create a timestamp, but other posts seem to say just an autonumber PK.

    I have updated the offending record from MySQL Workbench with no problem so the problem must be in Access.

    I have refreshed the table links and compacted the MS Access database.

    Is there something else I should be doing to diagnose this problem?

    Thanks,

    Ric


    Ric Miller

    Sunday, August 21, 2016 7:00 PM

All replies

  • Last week this happened to a client of mine, and the cause was a nullable bit field, which rarely makes sense.

    -Tom. Microsoft Access MVP

    Sunday, August 21, 2016 11:16 PM
  • Hi Ric Miller,

    According to your description, you could refer to You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000

    In addition you could try to relink My SQL Table or compact and repair your Access Database.

    Thanks for your understanding.
    Monday, August 22, 2016 8:44 AM
  • Hi Tom,

    That was the first thing I checked.

    I have bit fields but they are all set to default to 0.

    Thanks,

    Ric


    Ric Miller

    Monday, August 22, 2016 5:54 PM
  • Hi David,

    I relinked all tables and compacted/repaired but no change.

    I have bit fields but they are all set to default to 0.

    I do have some floats although they are null. I am just trying to type a date into a single date field so all float fields are null and bits default to 0.

    This is a very wide table.

    There are a bunch of blank records pre-created which of course have default bit values of 0 but all other fields are blank.

    There are a block of about 10 records near but not at the end of the table that have only default values that suffer from this problem.

    There is nothing special about them that differentiates them from other records with default values. You just can't enter a date or update a default bit value (or any field) without getting the error.

    Thanks,

    Ric


    Ric Miller

    Monday, August 22, 2016 5:59 PM
  • I have seen this message also when you attempt to update a record and then call another sub or function without using DoCmd.RunCommand acCmdSaveRecord before you call the sub or function and after you call it. Access considers "other users" to be more than human beings. I have had this message when I am the only user, have no bit problems and do not use SQL Server. For example if you have an After Update event on a field:

    DoCmd.RunCommand acCmdSaveRecord

    Call some function

    Exit Sub

    You can get this error when you recalc, refresh, move to another record or close the form. So check any form or record events that fire after you change a record.

    So you may need to:

    DoCmd.RunCommand acCmdSaveRecord

    Call some function

    DoCmd.RunCommand acCmdSaveRecord

    Exit Sub

    You may be getting this error because the record is not being saved prior to and after a call that updates records that are related to the forms record source. Access is telling you there is another human user changing the record, when in fact, it is a call procedure and the record has not been saved prior to continuing working in the form.

    I don't know if this is your situation, but it is another cause of this message.

    Monday, August 22, 2016 6:41 PM
  • Hi Lawrence,

    Thanks for the detailed help.

    I have completely bypassed the forms and code and went directly to editing a single field in a single record in the linked table, so there is no code involved.

    To make things more mysterious, I just dumped the database to another machine and relinked MS Access and the problem has majically disappeared.

    I guess what I will next is to restore the database to the same machine under another name, then change the ODBC driver to point to the new database and update the links.

    I'll report if that works.

    Doesn't seem like a great solution though as I would expect the problem to come back at some point.

    Thanks,

    Ric


    Ric Miller

    Monday, August 22, 2016 6:57 PM
  • If you have floats you should add a timestamp column.

    -Tom. Microsoft Access MVP

    Monday, August 22, 2016 10:24 PM
  • Hi Tom,

    I do have floats.

    I have a primary key named DbID that is of type auto_increment.

    Should I create an additional column named timestamp, that is not a primary key like this:

    ALTER TABLE `Table1` ADD `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP;

    Thanks,

    Ric


    Ric Miller

    Monday, August 22, 2016 11:33 PM
  • That's right. BTW, timestamp is on its way out. Rowversion is the new data type.

    Background info is here: https://blogs.msdn.microsoft.com/ssma/2011/03/19/access-to-sql-server-migration-access-solutions-using-sql-server-part-1/


    -Tom. Microsoft Access MVP

    Tuesday, August 23, 2016 12:00 AM
  • Hi Tom,

    I added a timestamp field to the table that was showing the error and it seemed to fix the problem.

    Then the user experienced the same problem but with a different record in the same table.

    The basic setup is that there is:

    1) A main form that is bound to a view of 6 tables all joined by the same ID primary key. There is a little data from each of the six tables on the main form.

    2) When you click one of 6 buttons on the main form a subform that is bound to 1 of the 6 tables appears

    3) The user enters data in the subform and dismisses the form when done. There are no events that I can see in this subform. You just add or change the data and click the X in the upper right corner of the form.

    This is where the error happens. It doesn't happen when you just change one field. But if you change multiple fields a couple of times each. You get the error.

    I have since added timestamps to all tables that get updated.

    And last I changed the MySQL driver to 5.3 ANSI instead of 5.3 Unicode. That seemed to help so I get the error less frequently, but I have gotten it at least once after I added the timestamps and changed the driver.

    Any ideas about how I could do things differently to vanquish this error?

    I should add that I didn't create this database or MS Access app. I migrated to MySQL and now I have to fix it.

    Thanks,

    Ric


    Ric Miller

    Saturday, August 27, 2016 11:56 PM
  • Is it an option to migrate to the free SQL Server Express edition?

    -Tom. Microsoft Access MVP

    Sunday, August 28, 2016 12:13 AM
  • Hi Tom,

    I should have checked this before I posted my previous message, but I get the same error even if I edit the data directly in the linked table in MS Access. So it doesn't seem to have anything to do with the forms.

    I open the offending table, go to the specified row, change each of two fields twice in the same record. When I click off the record to another record, I get the error.

    This table has a timestamp field and I have tried it both with Unicode and Ansi drivers.

    There are no bit fields although there are some float fields.

    Thanks,

    Ric


    Ric Miller

    Sunday, August 28, 2016 12:19 AM
  • Unfortunately my client is biased towards MySQL although if there is no way to make this work, I could probably get them to use SQL Server.

    Ric Miller

    Sunday, August 28, 2016 12:28 AM
  • Hi Ric Miller,

    According to your description, you could create a new database as simple as possible, then MySQL backend with an MS Access 2013 front end, could you reproduce this issue?
    Monday, August 29, 2016 7:09 AM
  • The only thing I would check then is if you re-linked the table(s) AFTER you added the row version column to the database.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Monday, August 29, 2016 6:51 PM
  • Hi Ric,

    I use this hack , And usually it helps.
    On Every control (that can be updated ) on the AfterUpdate event
    I Adds these lines:

    If Me.Dirty Then Me.Dirty = False
    DoEvents

    The reason for the "If Me.Dirty Then Me.Dirty = False"
    Is that it fire only if the record is not saved.
    There for error is avoided

    Good luck

    Asaf

    Monday, August 29, 2016 9:27 PM
  • Thanks for your reply. Yes, I definitely  relinked after I added the timestamps.

    Ric Miller

    Tuesday, August 30, 2016 4:20 PM
  • Thanks for your reply. The error happens even if I updating the record directly in the linked table. So there are no controls involved.

    Ric Miller

    Tuesday, August 30, 2016 4:21 PM
  • Hi Ric Miller,

    You could try to compact database to check that this issue is able to be resolved.

    Thursday, September 1, 2016 9:57 AM