Link to MySQL datetime as text

A newbie question here:

I'm linking to a MySQL backend table with a million+ records, where two
fields are of type datetime. However Access considers those fields to
be text. Can't seem to get Access to consider these date fields. Same
thing happens when I import a (smaller) table into Access from MySQL -
fields are converted to text.

An additional, possibly related symptom (though doubtful) is that some
records (10%) in a different table I've linked using Access are
displayed as #DELETED#, though they exist in the MySQL table. I can't
find any pattern why the particular records in the table are considered
deleted. In this case importing the table rather than linking "solves"
the problem (records are no longer deleted), but I really want to be
linking.

I'm using Access 2002, MyODBC 3.51.11-1, MySQL 4.1.9 and WinXP SP2,
MDAC 2.8 or some-such.

Very much appreciate any hints -
Peter

re: Link to MySQL datetime as text

On 26 Mar 2005 17:39:08 -0800, pholck@gmail.com wrote:
[color=blue]
>A newbie question here:
>
>I'm linking to a MySQL backend table with a million+ records, where two
>fields are of type datetime. However Access considers those fields to
>be text. Can't seem to get Access to consider these date fields. Same
>thing happens when I import a (smaller) table into Access from MySQL -
>fields are converted to text.[/color]

It looks like this is a bug in the version of the MyODBC driver you are using
(see http://bugs.mysql.com/bug.php?id=8384). Perhaps, try using a slightly
older version of the MyODBC driver?
[color=blue]
>An additional, possibly related symptom (though doubtful) is that some
>records (10%) in a different table I've linked using Access are
>displayed as #DELETED#, though they exist in the MySQL table. I can't
>find any pattern why the particular records in the table are considered
>deleted. In this case importing the table rather than linking "solves"
>the problem (records are no longer deleted), but I really want to be
>linking.[/color]

This problem is commonly encountered when linking Access to ODBC sources.
Generally, it means that the primary key of the table includes a data type
that is not absolutely precise or has greater precision on the server side
than in the Access equivalient data type - a floating point number, for
instance.
[color=blue]
>I'm using Access 2002, MyODBC 3.51.11-1, MySQL 4.1.9 and WinXP SP2,
>MDAC 2.8 or some-such.
>
>Very much appreciate any hints -
>Peter[/color]

re: Link to MySQL datetime as text

Thanks for the tips. I reverted to MyODBC driver version 2.5 (which is
listed as applicable for Win95 - and I'm on WinXP Prof - but that was
the only historical pre-compiled version available) and despite my
fears of who-knows-what old bugs I'll run across now, at least I can
link from Access to date fields!

Also you were correct on solving my #DELETED# record display: changing
my primary key in MySQL from type double to type integer solved that
issue as well.

Thanks!