MySQL Bugs: #25386: Cannot update TIMESTAMP or DATE columns

Bug #25386	Cannot update TIMESTAMP or DATE columns
Submitted:	3 Jan 2007 13:15	Modified:	23 Jan 2007 14:20
Reporter:	Tim Salter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Windows XP)
Assigned to:	MC Brown	CPU Architecture:	Any
Tags:	access, date, ODBC, timestamp

Description:
A field of type DATE or TIMESTAMP cannot be updated using linked tables in MSOffice applications.

This problem means that ODBC cannot be used to integrate MSOffice applications with any serious MySQL database, i.e. one that contains time/date fields.

How to repeat:
Create a MySQL table with a date or timestamp column.
Create an ODBC datasource using the above driver.
Create a new MSAccess project. 
Create a link to the MySQL table (File->Get External Data->Link Tables...)
Open the linked table in MSAccess
Try to edit the timestamp or date field in an existing record.
MSAccess then throws up an error saying that someone else is editting the record & it can't be updated.

Suggested fix:
Restrict the range of dates/timestamps so that the restricted range can be handled reliably.

Thank you for the bug report. That is a known issue with Access, please see
http://dev.mysql.com/doc/refman/5.1/en/myodbc-errors.html#qandaitem-26-1-6-3-7.

Thanks in advance.

Re the workround, in addition to the documented requirements, the timstamp field must be set to a non-null, not zero (i.e. not '000-00-00 00:00:00' value in order for it to work.

I've added a new question/answer combination to the documentation that deals specifically with this issue.