• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1011
  • 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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