Error deleting record in access

[roberto21]

I ran into the following error trying to delete a record from a linked table in accessfrontend access, backend mysql, tables migrated from ms sqlserver and relinked)


Microsoft Access
ODBC--delete on a linked table 'primanota' failed.
x
[MySQL][OD8C 9.1(w) number of attributes is larger than the number of attribute values provided (#0)

Note that deleting the record from that table using mysql workbench and the query DELETE FROM Table WHERE .... works OK. I could not find any hint in internet, What can be wrong here?
Microsoft Access
ODBC--delete on a linked table 'primanota' failed.
x


[MySQL][OD8C 9.1(w) number of attributes is larger than the number of attribute values provided (#0)

[June7]

Delete by what method?

Are you able to do other edits on table?

[roberto21]

Using the delete method I always used... opening the table in access, selecting the record, right click on it. delete record. It works ok on other tables.
I can insert into the table, no problem (see my other thread). And yes, if I change something in the record, no problem

[June7]

Sorry, no idea why manual delete would fail.

[Welshgasman]

Are there other records related to that table? Referential Integrity

[roberto21]

No, as far as I know. In fact, I want to delete a row in the "Ricevute" table, but I cannot do it because "ricevute" has a foreign key in "Primanota". So I try to delete the record in Primanota first, and get that error.
The only reference I found in the web mentions the error happening in the insert command, if there are more or less fields than present in the row. But of course this is not the case. Other references mention a not clear incompatibility problem between what? mysql server and driver? But why only in this table?

[roberto21]

I finally found the solution, thanks to a post in internet that seems to have a connection to the problem.
What happens is that for some reason access cannot delete a record from a linked table if that record contains Null values. I had a null field there, I inserted there a random value (zero) and voilà, the delete record worked fine.
I did not have this problem when I had the backend with MS sql server, so it must be a bug with mysql. The curious thing is that I got the hint from a post signalling this bug to mysql community on Oct. 22, 2024 related to the driver mysql odbc connector 8.4.

Bug #116442	Unable to Delete Row Manually in Linked Table with Empty Values in Microsoft Acc

https://bugs.mysql.com/bug.php?id=116442

I am using 9.1, and apparently the bug is still present...

[Welshgasman]

Thanks for posting the reason, as it makes no sense whatsoever to me, as to the logic.

[roberto21]

What does not make sense to you? The error? The reason? If it is a bug, it does not make sense at all.
What does not make sense to me is the fact that the mysql team states that the bug is "already solved in 9.1", whereas the error makes explicit reference to odbc 9.1.

[Welshgasman]

The fact that you are not allowed to delete a record with a field having a Null value.?

Edit: When a record is deleted, no concern should be given to the record contents.

[roberto21]

I fully agree with you. That is why I think this bug makes no sense at all, and the fact that it is still present makes even less sense. I hope someone notifies mysql team of the persistent bug, I would not know hoe to do it. At least, for now it is enough to be able to bypass the problem.

[davburk]

I am having this exact same issue with version 9.2 of mySQL ODBC connector . . . still not fixed.

[roberto21]

I am having this exact same issue with version 9.2 of mySQL ODBC connector . . . still not fixed.

Thank you for the post, at least I know I am not the only one tom face this problem. My question still remains: I don't know how to report this to mysql designers. Maybe the moderators here are more familiar with the procedure and know how to proceed?

[Micron]

When I don't know the answer to a question I often ask the Google god. Usually he answers.

https://dev.mysql.com/doc/refman/8.4...g-reports.html

[Oldvan]

I am having this exact same issue with version 9.2 of mySQL ODBC connector . . . still not fixed.

For me, adding a TIMESTAMP column to the table allows me to delete records without that dreaded error.

In MySQL Workbench, I ran this:

ALTER TABLE tblYackitySchmackity ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP FIRST;

Advice from here caused me to add this change:
https://dev.mysql.com/doc/connector-...bc-errors.html

Include a TIMESTAMP in all tables that you want to be able to update.

I hope this helps someone here!