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

[3 Jan 2007 13:15] Tim Salter
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.
[3 Jan 2007 16:46] MySQL Verification Team
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.
[3 Jan 2007 20:16] Tim Salter
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.
[23 Jan 2007 14:20] MC Brown
I've added a new question/answer combination to the documentation that deals specifically with this issue.