MySQL Bugs: #20359: 3.51.12 linked table in access 2003 displays improper time

Bug #20359	3.51.12 linked table in access 2003 displays improper time
Submitted:	9 Jun 2006 14:15	Modified:	7 Mar 2007 7:16
Reporter:	David Slone	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:	Windows (Windows XP Pro SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
I used mysql commands to make a mysql table from other mysql tables and one fo the columns in the table is a time and it displays properly if i view the table with MySQL CC but when i link the table in access 2003 and view the data from the MySQL table it shows the time in a date format. I had to update to 3.51.12 because my computer would not view linked tables with 3.51.10 even after i created a DSN to the mysql server.

How to repeat:
I assume one can create a table with a column data type as time and populate the information and then link the table in access and view the information either in linked table view or on a report.

Suggested fix:
data to show properly in the linked table view on access 2003

Hi Davis, thanks for your problem report. Sorry, I was unable to verify it as you can see on attached pictures.
Environment:
  MySQL server 4.1.18-nt-max-log and 5.0.22-log on localhost.
  MySQL Connector/ODBC 3.51.12, actual connect string: DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=test; USER=root;PASSWORD=;OPTION=3
  WinXP Pro SP2, fully updated
  MS Office 2003 fully updated

Test result with 4.1.x server

Attachment: 20359-4x1.jpg (image/jpeg, text), 100.91 KiB.

Test result with 5.0.x server

Attachment: 20359-5x0.jpg (image/jpeg, text), 96.36 KiB.

if you link a table that has a column type of time to access and view the linked table via access it shows the time as a date.

the field needs to just be time not a date/time or date

Sorry David, my mistake. Making new test.

Verified as described by reporter:
  MySQL server 4.1.18-nt-max-log and 5.0.22-log on localhost.
  MySQL Connector/ODBC 3.51.12, actual connect string: DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=test; USER=root;PASSWORD=;OPTION=3
  WinXP Pro SP2, fully updated
  MS Office 2003 fully updated

CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS timetest;
CREATE TABLE timetest (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, TimeToTest TIME);
INSERT INTO timetest VALUES (NULL,"00:00:00"), (NULL,"00:00:01"),(NULL,"00:01:01"),
(NULL,"01:01:01");

Result is show in attached images.

Image 1

Attachment: 20359-5x0-new-1.jpg (image/jpeg, text), 56.88 KiB.

Image 2

Attachment: 20359-5x0-new-2.jpg (image/jpeg, text), 52.32 KiB.

Image 3

Attachment: 20359-5x0-new-3.jpg (image/jpeg, text), 56.38 KiB.

Just wondered if there has been any progress on this, I've just been stumped by this bug for a while now.  Are there any work arounds yet?

This bug exposes the same problem that bug#25846 does. It has already been fixed. Therefore I am setting the status to "Duplicate"