Deleting/Editing rows in MySQL ODBC linked table results in error in MS Access

Question

I am using MS Access 2003 under Windows 7 (64bit), with external linked table at MySQL server (5.0.51a-24+lenny5), connected via MySQL ODBC connector (using 5.1.10, because the newest 5.1.11 is buggy). When I open this table in MS Access and try to delete some records from it, I get following error:

The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time.

When I try to edit some records in the table, I get following error:

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.

However, when I do it via deletion or update query in MS Access, it works fine! I just cannot delete the records directly from the table.

I found out (see the detailed analysis below), that the problem is present when there are double fields with values with a lot of decimal digits. See:

CREATE TABLE `_try4` (
  `a` int(11) NOT NULL default '0',
  `b` double default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

insert into _try4 values (1, NULL),(2, 4.532423),(3,10),(4,0),
     (5,6.34324),(6, 8.2342398423094823);

The problem is only present when you try to delete/edit the last record (a = 6), otherwise it is OK!

The issue is documented:

http://support.microsoft.com/kb/280730 , proposing these 3 workarounds:

• Add a timestamp column to the SQL table. (JET will then use only this field to see if the record has been updated.)
• Modify the data type that is in SQL Server to a non-floating point data type (for example, Decimal).
• Run an Update Query to update the record. You must do this instead of relying on the recordset update.

However, these 3 workarounds are not satisfactory. Only first could be, but this workaround didn't work - as expected. It probably works only with MS SQL Server.

Are there any other solutions/workarounds for this problem?

Additional details:

• The MySQL server is just for me, nobody else is accessing it.
• Insertion of new records was working fine.
• Primary key is well defined for this table.
• Restart of MS Access didn't help.
• Deleting the link to the ODBC table and linking it again didn't help either.
• Linking the table from brand new Access database didn't help.
• Changing the MySQL database engine from MyISAM to InnoDB didn't help either.
• There is no problem with permissions, there are all permission for this user@host.
• I can normally delete the records from the MySQL console at the server without problem.
• Trying to set MySQL Connector ODBC options didn't help: Allow big results, Enable automatic reconnect, Allow multiple statements, Enable dynamic cursors, Force use of forward-only cursors, Don't cache results of forward-only cursors.
• I turned on debugging in MySQL ODBC connector, it created myodbc.sql log, but it didn't contain any corresponding queries when editing/deleting (don't know why).

Answer 1

I solved here the same issue. The solution was to remove Default Values from decimal fields in the table. I was able to keep decimal data type but just remove the default value I already defined before with 0.0000 and now I set to null and bug fixed.

Answer 2

More details about the structure of the linked table would be helpful, but I'll hazard a guess.

I've had a similar problem in both MS Access 2003 and 2010 when I included nullable boolean fields in the SQL Server linked table. Seems JET databases have a problem with nullable nit fields. Check out this answer for more information: https://stackoverflow.com/a/4765810/1428147

I fixed my problem by making boolean fields non-nullable and setting a default value. If your problem is the same as mine, but with MySQL, try doing the same.

Answer 3

My workaround was to copy the table data into excel, then use phpadmin to clear the table, then do the editing in excel and copy the 'new' data (ie, all of it, after editing) back to access.