Code to Import a Multi Value Field in Microsoft Access

Posted on 2011-05-10
Last Modified: 2012-05-11
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.

Question by:tg4fsi
    LVL 47

    Accepted Solution

    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.

    Author Comment

    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?
    LVL 8

    Assisted Solution

    I totally agree with fyed.  You could use Split to create an array out of the Category IDs.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now