Microsoft Access can't update all the records in the update query.
Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to key violations, 883 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.
[Yes] [No] [Help]
So as you can see, there's lock violations meaning they can't be updated. Or are there? Nope, there aren't any lock violations.
In my search to find out what was going on, there seemed to be a variety of potential causes. 1) No primary key in the table being updated. I made sure there was a primary key and there was. 2) A setting in Microsoft Access locked the records. Nope, it's in "No locks" mode. 3) MySQL locked the table, run UNLOCK TABLES to sort it. No difference.
I was starting to give up, until I looked closely at the message that came up before the error:
You are about to update 889 row(s).
Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to update these records?
[Yes] [No]
What I didn't notice before was that 6 records were not affected by the lock violations. I opened up the SELECT equivalent of the query and reviewed the data. Turned out that those 6 records were the ones that would actually change their values, while the remaining 883 would remain the same.
So it sounds to me like Microsoft Access or the MySQL Connector is interpreting the server's response as refusing to update records, rather than "these records are already done mate, no point in doing it again". So clicking [Yes] on the error would update these 6 records and otherwise run the query as expected. However, this is not ideal in case there was an actual lock violation, so I modified the update query so that it wouldn't try to write anything to the field if it already contained the right value. Once I did that, no more error.
I don't usually post stuff on here, but it's worth sharing since there's no clear forum post or anything online about this, most go on about primary keys or just complaining about using Access as a front end for MySQL (often valid point, but not helpful when you're trying to fix a problem - looking at you Stack Overflow).
