Import from Excel 2003 into Access 2003

[Guest]

I imported an Excel spreadsheet with no problem using the import spreadsheet
wizard (Access 2003). It had 60 records. I wanted some more records so I
imported the same spreadsheet to the table Access created, this time I get a
message at the end saying it failed. So I go in both Access & Excel and
changed the field names, they are exactly the same, same names and same
amount of columns. It failed again but no explanation. Can anyone advise?

Thanks
Wm.

 

[Ken Snell \(MVP\)]

What error message did you get? What are the fields in the ACCESS table and
what are their data types? Are the fields in the table in the same order
(top to bottom in table design view is the same order as left to right in
EXCEL sheet)?

 

[Guest]

Ken, Hi. The error message is "An error occurred trying to import file
'C:\documents and settings\desktop\zz.xls'. The file was not imported. The
field names are ID-Auto, Date-Date, Extension-Number, TimeOfCall-Text,
Duration-Text, PhoneNumber-Text, Location-Text, Duration1-Number, Yes they
are in the same order top to bottom & L to R.

Thanks
Wm.

 

[Ken Snell \(MVP\)]

I assume that ID-Auto is an autonumber field? If yes, then the import is
trying to load the first column's data into that field. If the EXCEl file
contains a value in that column tha already exists in the table for that
field, you'll get an error.

I usually put autonumber fields "last" for such tables, and then don't
import a column from the EXCEL file for that column. So, for example, if the
table contains 6 fields with the autonumber field, then I would have only
five columns in the EXCEL file. This avoids such a problem because ACCESS
won't have data to try to put in that field and the field will be populated
automatically via the autonumber feature.
--

Ken Snell
<MS ACCESS MVP>