Import Excel File into Access 2003 database and getting errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neal - 06 Jul 2005 18:59 GMT

Dear All,

I created a database in Access 2000 and was able to import data from an
excel file into an existing table without a problem. We recently switched
over to Access 2003 and I tried to import an excel file which is set up
exactly like the previous excel file. I received an error message. So I
imported the file into a new table and copied it into the original table and
it worked. What could be the reasons for the errors in the import of the
excel file into the existing table? Thanks.

Neal

Reply to this Message

Ken Snell [MVP] - 06 Jul 2005 22:40 GMT

What were the errors?

Signature

Ken Snell
<MS ACCESS MVP>

> Dear All,
>
[quoted text clipped - 8 lines]
>
> Neal

Reply to this Message

Neal - 07 Jul 2005 18:06 GMT

Ken,

The error only says that there were errors importing the file and that the
import was not successful. It does not specifiy why.

Neal

> What were the errors?
>
[quoted text clipped - 10 lines]
> >
> > Neal

Reply to this Message

Ken Snell [MVP] - 07 Jul 2005 20:21 GMT

It may be that ACCESS 2003 is "assigning" a different data type to some of
the EXCEL columns than ACCESS 2000 does. When importing EXCEL data, it's
almost always best to import to a new table, then use an append query to
copy the data to the permanent table -- that gives you more control over
data formatting, etc.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 21 lines]
>> >
>> > Neal

Reply to this Message

Dodo - 07 Jul 2005 12:12 GMT

> Dear All,
>
[quoted text clipped - 8 lines]
>
> Neal

Do the formats of data in Excel and Access match?

I do not import but link to Excel and CSV files. Similar problems occur as
Access does not always correctly recognize the format of a field in the
source.
This then has to be modified by hand in the definition of the link.

Another source for trouble with reading an Excel sheet is the formatting of
a column in the sheet.
If by any chance the cell formatting in a column changes down the column I
have had problems in getting all the data available in Access (or was it in
Crystal Reports?).
Such formatting differences are sometimes difficult to trace when numbers
are both entered as numbers and in other cells as right aligned text.
You will then get an error message indicating that the format of the data
does not match the format of the field it is used in.

Reply to this Message

Neal - 07 Jul 2005 18:09 GMT

Thanks for the ideas. The crazy thing is the excel file is coming from
another Access database as a query so I thought that the data would be
consistant.

Neal

> > Dear All,
> >
[quoted text clipped - 25 lines]
> You will then get an error message indicating that the format of the data
> does not match the format of the field it is used in.

Reply to this Message