MySQL Bugs: #9211: Access: ODBC - Update on a linked table table

Bug #9211	Access: ODBC - Update on a linked table table_name failed
Submitted:	15 Mar 2005 23:00	Modified:	10 Aug 2005 15:42
Reporter:	Miguel Solorzano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.11	OS:
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
With particular MyISAM tables and using an DSN checking the option
Don't Optimize Column Width, Access can't update linked table and
displays the message error:

[Microsoft][ODBC Driver Manager] SQL Data Type out of range #0

How to repeat:
1- Create and populate the below table using a server 4.0.23.

CREATE TABLE `csctest_c` (
  `col1_no` int(11) NOT NULL auto_increment,
  `col2_from` varchar(30) default NULL,
  `col3_to` varchar(30) default NULL,
  `col4_time` int(15) unsigned default NULL,
  `col5_date` date default NULL,
  `col6_co` varchar(30) default NULL,
  `col7_make` varchar(5) default NULL,
  `col8_delivered` char(1) default NULL,
  `col9_build` date default NULL,
  `dummy_stamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`col1_no`)
) TYPE=MyISAM PACK_KEYS=1; 

insert into csctest_c (col2_from,col3_to,col6_co,col8_delivered)
values ("xxxxxxxxxxxxx","yyyyyyyyyyyyy","USA","y");

insert into csctest_c (col2_from,col3_to,col6_co,col8_delivered)
values ("xxxxxxxxxxxxx","yyyyyyyyyyyyy","USA","y");

insert into csctest_c (col2_from,col3_to,col6_co,col8_delivered)
values ("xxxxxxxxxxxxx","yyyyyyyyyyyyy","USA","y");

2- Create an DSN with Windows ODBC Manager and checking the
option Don't Optimize Column Width.

3- Create a new Access DB and using the DSN above link the
table  csctest_c.

4- Open the linked table and try to edit the column col8_delivered
and you will get the message error as showed in the attached file.

Suggested fix:
--

Shows message error

Attachment: CSC4697.PNG (image/png, text), 55.06 KiB.

I too have this same problem with Access, ODBC drivers, and linked tables. I have tried various versions (that I could find) of the ODBC drivers with the same result. What I would say is that (in my tests) the problem only seems to occur if the table has one or more TIMESTAMP column(s). If the table only has int and varchar columns, this problem does not seem to occur.

Log files

Attachment: logs.zip (application/x-zip-compressed, text), 50.74 KiB.

shows bug at c - level. exec like > test "DSN=MyDsn;" and see error

Attachment: test.c (text/plain), 4.65 KiB.

This also occurs with MS Access 2000 SR1 SP3. using Windows XP Pro SP1.

Adding a record has no problem, but deleting or updating a record does. How to I roll back to 3.51.10?

I also get this. It's really nasty. 
I can confirm it only happens in tables with fields of DATETIME or TIMESTAMP type. Especially mean since you need to add TIMESTAMP fields in order to get around the #DELETED# problem.
Interesting also that the linked tables show a datatype of String instead of Date/Time in Access. This obviously wrong datatype mapping might be the cause for the problem.

It basically makes it impossible to use Access as a Frontend for mySQL.

Miguel,
check bug 8363.
It seems to have been solved.

The bug was fixed. I placed new files on http://www.infocentr.ru/~wax/myodbc3.zip for testing. Will you (or anybody) be able to test it ? I want to be sure that I fixed it right. If all is ok I'll  insert this changes in distributive.

Wax,

I wasn't able for to test. Please see note in the Associated support issue.

Access can't handle 'int unsigned' fields - they're too big.
Try converting to either 'int' or 'mediumint unsigned'.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Is this supposed to be fixed in 3.51.12? I just upgraded and I am still receiving the same issue. Is there a work around?

I am having this exact problem with Access 2003 running on a win xp machine. I am linking to a 3.51 mySQL db running on a win server. Any help would be greatly appriciated. I have to abandon my plans of using a mySQL back-end if I can't use the legacy Access application that my users are accustom to.  Any work arounds???

I am having this exact problem with Access 2003 running on a win xp machine. I am linking to a 3.51 mySQL db running on a win server. Any help would be greatly appriciated. I have to abandon my plans of using a mySQL back-end if I can't use the legacy Access application that my users are accustom to.  Any work arounds???

I am having this exact problem with Access 2003 running on a win xp machine. I am linking to a 3.51 mySQL db running on a win server. Any help would be greatly appriciated. I have to abandon my plans of using a mySQL back-end if I can't use the legacy Access application that my users are accustom to.  Any work arounds???