If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Back End Tables
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.... |
#2
|
|||
|
|||
Back End Tables
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 "PeterM" wrote in message ... 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.... |
#3
|
|||
|
|||
Back End Tables
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! "Arvin Meyer [MVP]" wrote: 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 "PeterM" wrote in message ... 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.... |
#4
|
|||
|
|||
Back End Tables
On Mon, 5 Dec 2005 20:25:03 -0800, PeterM
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
preventing direct access to the back end file on a network | Paul Ponzelli | General Discussion | 10 | September 29th, 2005 01:23 PM |
preventing direct access to the back end file on a network | Paul Ponzelli | Database Design | 10 | September 29th, 2005 01:23 PM |
Macro for Pivot Tables | Thomas | General Discussion | 1 | March 15th, 2005 01:03 AM |
need to join local tables with sql 7 tables using guids | Susan via AccessMonster.com | Running & Setting Up Queries | 0 | January 31st, 2005 08:58 PM |
Need help linking 2 tables for entering new & editing existing rec | scott | General Discussion | 2 | January 18th, 2005 10:09 PM |