Bug #10530 MERGEd tables w/DATETIME in primary key -> ODBC call failed in Access link-tbl
Submitted: 11 May 2005 7:13 Modified: 26 Jul 2007 17:30
Reporter: Joe Calkins Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51 OS:Windows (XP sp2)
Assigned to: CPU Architecture:Any

[11 May 2005 7:13] Joe Calkins
Description:
When opening linked table in MS Access 2002 SP3 (linked to MERGE table in MySQL), error msg box reports "ODBC--call failed." I get 88 to 92 rows with "#Name?" showing in every column in every row.

How to repeat:
This only fails for MERGE tables whose source tables have a DATETIME type field in the PRIMARY KEY.

1) Define MERGE table in MySQL 4.1.11-nt:
CREATE TABLE `nonsymbols` (
  `NewsTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `Allocated_Symbol` varchar(12) default NULL,
  `Category` char(1) default NULL,
  `Resolved` tinyint(4) default '0',
  `Ignore` tinyint(4) default '0',
  INDEX (`NewsTime`),
  INDEX (`Category`),
  INDEX (`Allocated_Symbol`)
) TYPE=MERGE UNION=(a_mw.nonsymbols[,any other tables to add in]);

2) Also create a MERGE table using a table that does NOT have a DATETIME type in the primary key.

3) Set up an ODBC System DSN using MyODBC 3.51:
    x Don't Optimize Column Width
    x Return Matching Rows
    x All Big Results
    o Force Use of Named Pipes

4) In MS Access, select File / Get External Data / Link Tables / Files of type = ODBC Databases() / Machine Data Source / <select the new DSN> / <select the tables to create links to> / <select the fields that correspond to the primary keys>

5) Still in MS Access, open the table that does NOT have a DATETIME type in its primary key--it should open fine. Now open the table with the DATETIME type in its primary key. This should produce an error message box with "Microsoft Access" in the title bar and "ODBC--call failed." in the message area. Upon clicking "OK" you'll see your table open with a maximum of 88 to 92 rows and each column in each row showing "#Name?" instead of the correct data.

6) You can still do operations with the tables, like creating a query "select count(*) from nonsymbols;". This gives valid results.

7) Create a linked table from MS Access to one of the source tables for the MERGE table, and you'll find that MS Access can read the table data just fine.

Suggested fix:
Emulate keys in MERGE tables. 

Speaking from my lack of hubris and belief in my own superiority, I suspect that this is a bug in MS Access in the code that handles the user-assigned unique fields at the time of creating the linked table. Unfortunately the fact that the error comes from the ODBC driver muddies the waters.
[11 May 2005 20:05] Joe Calkins
`NewsTime` is the only field in the primary key in the source table for the MERGE table `nonsymbols`.
[13 May 2005 18:10] MySQL Verification Team
Thank you for the bug report.
[13 May 2005 19:28] MySQL Verification Team
The same behavior I got with the MyISAM tables, so isn't related
to the Merge tables only.
[30 Sep 2005 13:24] teej TJ
Using 4.1.7-NT I'm getting the same issue connected from MS Access 2003 SP1 via ODBC MySQL Connector 3.51.

I have a small database with one table containing about 30 columns, one of which is a DateTime declared as a primary key.

ALTER TABLE `nuclear`.`explosions` DROP PRIMARY KEY,
 ADD PRIMARY KEY(`ID`, `Codename`, `DateTime`, `Site`, `Classification`, `Purpose`, `Yield`, `DeviceType`, `Country`);

Simply trying to open a view to this table causes Access to report "ODBC call failed".

If I remove the DateTime field from the Primary Key list on MySQL server, Access can successfully read the table.

ALTER TABLE `nuclear`.`explosions` DROP PRIMARY KEY,
 ADD PRIMARY KEY(`ID`, `Codename`, `Site`, `Classification`, `Purpose`, `Yield`, `DeviceType`, `Country`);
[24 Oct 2005 22:46] Peter Harvey
I am not able to reproduce these problems. But this can mean that I am not quite getting it or that c/odbc v3.51.12 fixs them. The latter is quite likely since it has fixs for working with date/time and for determining the root table name. Please try Connector/ODBC 3.51.12 from www.mysql.com.
[26 Jul 2007 17:30] Jim Winstead
Going with what Peter Harvey said. This bug has likely been fixed since reported, but the bug also does not have enough information for verification. (For one thing, the example table does not have a primary key.)