Type Conversion Failure in Access 2003 when importing from Excel

[Guest]

I have a database created using Access 2000 that imported data from Excel
without any errors. Now that I have upgraded to Access 2003, the import
contains errors. I have also upgraded the mbd file to 2003 but it did not
solve the problem. I have successfully troubleshot?? troubleshooted?? the
problem but wonder if there is some easier work around than reformatting the
data.

The table I import to contains a column with the TEXT 255 data type while
the Excel file column has the format GENERAL. In the data for this column,
some of the rows contain numbers and some contain alphanumeric data. Access
2000 used to import it all as text but Access 2003 seems to follow whatever
datatype is characterized by the first row of data. So, if the first row
happens to have a number in that column, then all the rows that have
alphanumeric get rejected. If the first row has alphanumeric then everything
imports without errors.

Any settings I need to change or do I just need to make sure the first row
of data has alphanumeric?

 

[John Nurick]

Hi wocruc,

If you can easily make sure the first row contains data that can't be
interpreted as numbers, that's probably the simplest thing to do. For
the settings that control how the data is scanned to guess at the field
types, see

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
http://support.microsoft.com/?id=257819

 

[Guest]

OK, thanks for the help. It seems strange that it requires this. One would
think it would just load data using the format the table is expecting.