MySQL Bugs: #61024: LONGTEXT and TEXT fields are converted to text(255) in Access 97

Bug #61024	LONGTEXT and TEXT fields are converted to text(255) in Access 97
Submitted:	2 May 2011 13:31	Modified:	9 Aug 2022 11:28
Reporter:	Daniel Aschauer	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.8	OS:	Windows (XP)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	access97, longtext, MEMO, text

Description:
The bug was already described as #40932:
http://bugs.mysql.com/bug.php?id=40932

When a table from the mysql 5.x database is linked in Access 97, fields of type TEXT OR LONGTEXT are translated to TEXT in Access (which is limited to 255 chars), when it should be MEMO.

How to repeat:
- in the mysql database, create a table with LONGTEXT or TEXT field
- create a MyODBC 5.1.8 DSN
- open access 97 and link the above table using the above DSN
- look at the table structure, the LONGTEXT/TEXT field is shown as a Text field of length 255 and it accepts only 255 chars.

Suggested fix:
The bug was reported to be fixed, but doesn't seem to be the case?

Daniel,

Thank you for reporting the bug.

I tested with MyODBC 5.1.5 and TEXT fields were reported as MEMO.
With the driver version 5.1.8 the same fields are shown as TEXT(255) in MS Access 97. Looks like the problem has been re-introduced somewhere after 5.1.5.

Setting the bug status to VERIFIED.

Strange, because in the former bug report http://bugs.mysql.com/bug.php?id=40932 the bug is mentioned fpr 5.1.5 and reported to be fixed with 5.1.6
Are there other factors (version of mysql-server?).

I am now running ODBC 5.1.8 and MySQL Server 5.5 on a 64 bit Windows Server 2003.  I can confirm that this is still happening.  

We are using UTF8 and primarily InnoDB tables with a small number of MyISAMs.

When I use ODBCAD32 (which, incidentally, has to be the one in the ironically titled SYSWOW64 folder, which can be seen by 32 bit software like Access97), if I go into the Details page in my DSN and set character set to UTF8, the fields are recognized as Memo and not Text 255.  But then the data themselves are a bit mangled when it comes to special characters.

So the workaround I'm using for now is to create the Link tabledefs with this UTF8 setting, and then remove the UTF8 setting without recreating or doing a refreshlink.

But it would be really, really great if this would just go away. :)

Does posting this bump message work as a bump?

I just upgraded to Connector 5.1.11 and the problem is still quite present.

I know Access 97 is ancient, but there are still enough users out there that at least two of us have gotten all the way to these bugs to mention our vexation.

We probably represent many more.

Posted by developer:
 
MS Access 97 is an obsolete product.
This problem does not happen with MS Access 2007 and newer.
The bug is closed.