CODEWITHSUNDEEP
ms-accessvba
user6576693
user6576693

Reputation: 3

Import excel records into access based on column value

I am a newbie with access and I am trying to import records into several tables from an excel file. Each row in excel has different number of columns, but the good thing is column A is able to help me to identify what records need to go to my different tables.

Sample table

As you can see in the picture, Row 1 Column A has the value of "H", which would indicate that this record needs to go to the "H" table. Then the next few rows have a value of "R" in Column A which indicates that these records should go to the "R" table, and so on and so forth. However, the number of records to be imported into each table will vary all the time. Like the sample above rows 2 through 10 belong to the table R, but the next import may have only 5 or 20 records.

Currently I am using a temporary table and using an append query for each table but I am wondering if there is an easier way via VBA or other method that could be faster and more efficient.

Thanks!

Upvotes: 0

Views: 404

Answers (1)

ASH
ASH

Reputation: 20342

The way you are doing it now may be the best way. An alternative would be to do this in two steps:

1) split your column A, and parse out to different sheets (or different workbooks).

http://www.rondebruin.nl/win/s3/win006.htm

2) load those different sheets (or workbooks) into different tables.

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpAllWktsSepTbl

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFldWrkFiles

Upvotes: 0

Related Questions