OK - this is helpful!
When you want to import a spreadsheet into an existing table, the table must
have exactly the same number of fields as the spreadsheet, and the fields'
names must match the header names in the spreadsheet. In your case, I think
the problem you're having is that you have a primary key field in your
table, but the spreadsheet does not. Therefore, ACCESS is trying to import
the first column of the spreadsheet into the primary key field (assuming
that the primary key field is the first field in the table), and I'm betting
that header name is not the same as the primary key field's name and that
the format of the first column is not the same as the format of the primary
key field.
When I need to import a spreadsheet, I will usually import it into a new
table, and let the import wizard create the table for me. Then I use an
append query to copy the data from that new table into an existing table.
This allows me complete control over which fields map to which, over the
format of the data as I put the data in the permanent table, etc.
Try importing and choose the "new table" option. Then create an append query
to copy the data to the existing table. You then can delete the "new" table.
--
Ken Snell
<MS ACCESS MVP>
erin said:
Thanks for replying. I'm relatively new to the program so it might be a
simple problem. I seem to have gone beyond the scope of the step by step
books available to me. The error that I am getting states: An error occurred
trying to import "then lists file address". The file was not imported.
I've tried to find some sort of error explanation as to what occurred but can't find anything.
Basically I created a table and form first in access based on the types of
reports and querys I thought would be done. Then I purchased a marketing
list in an excel format as a starting point for data entry and tried to
import it into the table which I created. To import I went to File: Get
External Data: Import then selected the excel file. The Import Spreadsheet
Wizard then popped up. I checked the box that says the First Row Contains
Column Headings which it does. It then asks if I'd like to put the file
into a new or existing table. I selected existing and chose the table. The
wizard then says that's all the information it needed and says import to
table. I hit the finished button and then the error above is displayed. I
was never prompted to specify a primary key and I was never asked to specify
how the existing columns in excel match the existing columns in my access
table, which I thought was strange.
The existing access table includes fields such as an ID field as a primary
key, date of entry, name, address, phone number, etc. There are also fields
describing more about the company such as size, projects worked on, services
offered etc. These do not match with any information in the excel file. The
excel file I'm trying to import includes columns such as name of firm,
address, phone number, etc.
Is there a way to explain to access how and where I'd like the excel
columns imported into the access table? I hope I've explained the situation
thoroughly enough. Please let me know if you have any ideas about where I'm
going wrong. Thank you!
