Importing Excel data into Access

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Folks,

I'm new to databases, and created my first one by taking the Access Template
for Client Management from MS web site, adding a few fields to it and
creating a Customer Management DB to track interactions with our clients.
My boss now wants me to go back through Excel spreadsheets that were used
previously to capture this data and bring it into the new database. I'm
looking through this discussion group and finding bits and pieces and will
continue to look, but wanted to ask the question anyway.
It seem like if I have a table in my DB with info like FirstName, LastName,
Address, City, State, Zip, e-mail address, etc and I had the same fields in
the Excel spreadsheet (Last Name, First Name, Address ,etc), that would be my
starting point. Is that right? Does everything have to match (i.e. all of
the fields in the Excel sheet have to be in the access table and vice versa)?
Does it automatically create the new record while importing?

Any advice on how to start this would be appreciated.

Mike
 
Mike,
yes the field names must match exactly.
All the imported data will end up in your table in access, unless you have
an index on the table that excludes duplicates.
You probably don't have such an index, so won't need to worry about it.

The biggest hurdle with importing from excel into access is with the fields
of number data type in your access table.
For a table with names and address info, it would be best to have all the
fields as text data type - including phone nos and zips.

If your fields in the access table are all text, then go to each spreadsheet
and insert a new row immediately below the column headings.
In each column type XXX in the first row. This has a good chance of making
the export process treat each column of the spreadsheet as text to prevent
problems with the import. After you have finished the imports, you can find
the rows with XXX in them and delete them from the access table.

Post back when you need to.


Jeanette Cunningham -- Melbourne Victoria Australia
 
Peter,

Thanks for the link, but I'm getting an error on it.
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32

Am I doing somethign wrong?

Mike
 
Back
Top