Re: ODBC - insert on a linked field - [microsoft][ODBC Driver Manager] SQL data type out

[Mark Schiavone]

I had this problem too. Built the tables and forms in Access, then used Access-to-MySQL to convert the tables into MySQL. The date fields were typed as DATETIME in MySQL, and in Access the data types in the tables mapped to TEXT. I'm using a system DSN to connect the two through ODBC-linked tables. The account was granted INSERT and UPDATE permissions on the table. But each time I tried to create a new record, or modify an existing one, I was getting this error.

My fix was to convert the DATETIME fields in MySQL to VARCHAR(20). I then modified the Default property on any affected form's date/time field to =FORMAT(Now(), "yyyy-mm-dd hh:mm:ss"). I have some fields which stamp a date into a field and applied similar logic.

Lastly, and I'm not sure why this is important but it was the only thing that got the app running was to delete the table link in Access and relink the table. Works fine now.

Hope this helps folks!