Back End Tables

[Guest]

Our company uses a host to store our back end tables for Access 2002. I
create the tables in Access locally, then use the Export feature to export
the table to the back end host via ODBC definitions. Currently we have over
25 tables on the host...the host uses MySQL as the database.

I recently created a new table in Access and exported it to the host. I
then linked (using the Link Table manager) back to the Access database. I've
done this for a ton of tables with no problems. When I look at the table on
the host, all of the date/time fields have been changed to varchar(40)
fields; only the date/time fields are messed up. I change them on the host
back to a date/time field and the table data looks fine. I link the table to
the Access database and when I look at the table in Access, it changes all
Date/Time fields back to text. Also, when I try to open the host table in
Access, I get an ODBC error.

I've checked with the host and they claim no changes on their end...can
anyone offer any advice to help?

thanks in advance for your help....

 

[Arvin Meyer [MVP]]

Typically, this is caused by a date which is incomplete (like month//year)
or out of Access SQL format (mm/dd/yy) in the first 8 to 25 rows. If it
happens later, Access will add an error table. You can use text dates in
Access, and change them to true dates with the CDate() function so that you
can use them in calculations. A date in Windows or DOS is a special Double
datatype. MySQL may see it differently.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thank you for the explanation Arvin...

this presents a significant problem for me...while I can use cDate within a
form or report, I can't use it for queries...specifically while populating
listboxes. With the date being stored as text, it makes it impossible to
sort by the text date within the listbox...

01/01/2006 comes before
12/01/2004

any ideas? thanks again!

 

[John Vinson]

while I can use cDate within a
form or report, I can't use it for queries...

Um? Sure you can. Just put

SortDate: CDate([textdate])

in a vacant Field cell in the query grid.

John W. Vinson[MVP]