Import Excel Spreadsheet into Existing MS Access Tables

Question

I have an Access database. Here is the setup for a few tables.

id - companyID (autonumber) PK, company (text), firstName (text), lastName (text)

category - companyID (number) combined PK with category, category (text)

salesmen - companyID (number) combined PK with code, code (text)

There is a 1-many relationship between id and category and between id and salesmen.

If I have a spreadsheet with columns of company, firstName, lastName, category1, category2, category3, salesman1, salesman2, how could I import the columns into the appropriate tables?

My first idea was to import the spreadsheet and then append company, firstName and lastName to the id table. Then I would join the imported spreadsheet with the id table to create a new table with all of the spreadsheet columns plus the auto generated companyID. Then I could append companyID and category1 to the category table. Then do the same for category2 and 3 and so on.

This seems really complicated if I have a lot of spreadsheets to import. Also, the person who will be importing the spreadsheets isn't a programmer, so she wants it to be as user-friendly as possible.

Is there a better way to import these spreadsheets?

Thanks!

Solution

What I would do is create another table to import the raw data into, then INSERT the data from there into the relevant tables.

DoCmd.RunSQL ("DELETE * FROM ImportDataTable;")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ImportDataTable", "C:\exceldata.xls"

The second line in Access VBA will import the data into the table called ImportDataTable (the ImportDataTable column names should be F1, F2, F3, etc. Then use an append query (INSERT INTO) for each table that some of the ImportDataTable data needs to go in to. All this code can be put behind a button on a form so that the user(s) only need to press a button when new data is available.