MySQL Forums
Forum List  »  Microsoft Access

Re: ODBC - insert on a linked field - [microsoft][ODBC Driver Manager] SQL data type out
Posted by: Mark Schiavone
Date: April 29, 2005 02:09PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: ODBC - insert on a linked field - [microsoft][ODBC Driver Manager] SQL data type out
2990
April 29, 2005 02:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.