Access multivalue field convert to MS SQL

Question

I am working on preparing to move my back end tables to MS SQL. One of the data types I have in Access are a multivalue fields. I heard ms sql does not support multi value data type, is this true? If this is the case, what are your recommendations for me to convert my Access multi value fields to be compatible with Ms Sql data type? I plan to use Access as the front end after moving the tables to sql, so I need the conversion to go smoothly to ensure integration between the two work. Any advice is appreciated.

Answer 1

Unfortunately there is no automated conversion.

What this means is that before you migrate the data to SQL server, you have to make changes to Access, and 100% make sure things work BEFORE you attempt moving to SQL server (this “before” step is most important).

The other issue is you will lose the “very” cool multi-select combo box, and list box.

What this means, is that a combo box like this:

Becomes this:

It still a kind of click + choose the combo box, but certainly not as nice as the cool multiselect combo box. Hopefully you only have say 3-4 of these. So how much work this will be will depend on how many places you use the "mv" system.

The steps are:

Create a new related child table. (to hold multiple values).

In above, we thus created a new "child" table called:

MyFavorateColors

Write some VBA code to pull or migrate the data from “mv” column to the new table. Remove the MV combo (or listbox) from the form, and now create  sub form (multiple items is a good choice). Drop in a combo box, and use the wizard (but don't choose multiple items). Now just like some invoice, or anything else, you are free to "enter" multiple choices for the color choice. Each new "choice" will of course results in a new row of data in that new table.

Creating the sub form + combo box can be done without code, and with just wizards. However, the moving of data will require some VBA code.

Once all of the above works in standard “tables”, and no MV columns exist (and lookups), then you can migrate the data to SQL server.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 2

Thank you for your advice, Albert. That is the direction I am heading based on my research. Thank u for confirming that SQL does not support multivalues. I have quite a bit of multi value fields, thus the number of tables will double or even triple.