Importing Excel data into multiple tables (1 Viewer)

Hi guys/gals,

Can anyone tell me the quickest way to do the following.

I have a contact access database which need a regular feed from maillist spreadsheets received from data suppliers, and I need to build an import wizard. The excel spreadsheet contains the following data headings:

Customer Name, Customer Address, Customer phone number, Contact Name.

There is already data in the access database, but I know need to add to the existing tables with these import feeds.

EXISTING ACCESS TABLES
====================
1. Customers

CUSTID (autonumber)
CUSTNAME
CUSTADDRESS
CUSTPHONE

2. Contacts

CONID (autonumber)
CONCUSTID (Foreign key to customers table)
CONNAME

PROBLEM
=======
As you can see from the excel spreadsheet there are multiple entities on one line, and I need to map that spreadsheet data into the tables as follows:



Excel column Database column

1. Customer Name Customers.CustName
2. Customer Address Customers.Custaddress
3. Customer phone number Customers.Custphone
4. Contact Name Contacts.Conname

Naturally the contact.conname needs to be populated with the concustid foreignkey.

Sorry for the long-windedness but hopefully all the information is there for you to help a stranded soul!

Thanks a lot...

You will have to do it by using VBA

Regards

Does anyone have a copy of similar code?

If your feed does not include a unique customer identifier, you will not be able to easily distinguish changes to an existing customer from the addition of a truely new customer.

You can link to the spreadsheet and join the spreadsheet to the existing table on customer name to update and add new records to the customer table. See the referenced article:
ACC2000: How to Update and Append Records in One Update Query

To update/add the contact records, you'll need to use the same kind of query but you'll need to join the linked spreadsheet to the customer table on customer name and then to the contact table on customerID to update the correct contact record.