• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 950
  • Last Modified:

Code to Import a Multi Value Field in Microsoft Access

I am in need of some VBA code to import and convert data into an Access multi-value field.

Yes, I know I should not be using them but I have no choice.  I work in ecommerce and I get CSV exported data in the following format:

ProductCode, CategoryIDs

"Red Widget", "1,3,5"
"Blue Widget", "3"

CategoryID, CategoryName

"1", "Furniture"
"3", "Tables"

I need to run data analysis on our store so I need to bring in the data and create the relationships.  I will also need to eventually export the data in the same format which is why I am staying with the multi-value field.  Since I cannot import into a multi-value field I figured this method would work:

1. Import the Products into a temporary table: _tblProducts
2. Using VBA code, append the data to a Products table and at the same time populating the multi-value field with the category IDs.

So I'm looking for the code that will cycle through _tmpProducts and append the data to the table Products while properly filling Products!CategoryIDs multi-value field.

2 Solutions
Dale FyeCommented:
Personally, I'd create a table (tbl_Product_Categories) and parse the values in the CategoryID's column of your Products table so that you have:

Product           CategoryID
Red Widget        1
Red Widget        3
Red Widget        5
Blue Widget       3

You can always concatenate these back into the format you need when you export.
tg4fsiAuthor Commented:
Yeah I've thought about that...I know it is the proper way of doing it.  I guess what slows me down on that is one store I deal with has 100,000 products and each product could be apart of 5-10 categories so that many to many table could be gigantic.  But Access simply does this in hidden tables anyways for multi-value fields, right?
I totally agree with fyed.  You could use Split to create an array out of the Category IDs.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now