I've recently run into one of the most frustrating bugs I've seen with a Microsoft Access integration with MySQL. What happens is that if you type something in a record, and then quickly move to the next record and type the exact same thing, the auto increment field appears to duplicate itself. This can happen not just twice, but many times to where you have several records with auto-incremented fields that appear to be duplicated. If you go to edit one of the record with the duplicated auto-increment field, it will update not just that one record but all the records that have the duplicated auto-increment field. If you go to try and delete one of the duplicates, it will delete the one and then show a #Deleted# error message on all the others. If you close out of the table and then come back in, it will show individual entries with different auto-incremented fields for all the records you had been working on.

I've tried getting a different ODBC connector and this didn't seem to change anything. I tried repairing the table, but that didn't fix it either. Also, I tried other tables and found the same issue. One thing that I noticed is that if I left a decent chunk of time before moving to the next record it didn't have the issue as frequently. The way I could most consistently reproduce the issue is if I typed just the number "1" in an amount field and then used the arrow down key on my keyboard and typed another "1" and then arrowed down and continued the cycle. The faster I did this the more consistent the error, but the error still happened sometimes even when I wasn't moving real fast between the records.

I had the error both in Access 2002 and Access 2010. I've tried MySQL ODBC connector 3.51.18 and 3.51.30. I should note that the auto-incremented field is also my primary key.


My somewhat ugly, but sufficient answer was to force each record to be unique. On the "Before Insert" event on the form using the table, I input one of the fields with the CurrentRecord number. I make it a negative number, which normally wouldn't be in that field and then clear it out later. This is currently working for me and I plan to just keep it that way unless someone is able to help me understand the fundamental issue that is causing the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.