In a Microsoft Access database, you can create a field that holds multiple values.

An example of this would be where an Interviewer may be recording multiple topics dealt with in an interview with a client. Ordinarily, a combobox would enable selection of a single topic from a list, but using a multivalue field allows selection of many topics.

While this is a quick and flexible method of storing data in an Access database, upscaling to a SQL Server backend presents a problem. SQL Server does not support a multivalue data type, and so before migrating data from Access to SQL, there are two major tasks to be resolved:

  • the multivalue fields must be removed and replaced with many-to-many relationships and a new table
  • the standard combobox that allowed multiple list selections must be replaced with a custom built alternative

    • Blue Bridge has a quick and easy solution, and the user sees little difference between the old multiselect combobox and the new multiselet button shown in the screenshot.

    You can download a working example of the Blue Bridge solution here. When you unzip the file, you will also find a document describing the upgrade process in detail. The document also includes notes on ways to transfer existing data from multivalue fields into your new many-to-many table.

    Multivalue

    Implementation of the solution is straightforward:

    • Download the solution zip, and import all the objects from SQLMultiselect.accdb.
    • Enter the values for each multivalue field into table tlkoLookupLists.
    • Replace your multivalue combobox with a copy of the MultiSelect Command button and its matching text box.
    • Change the control source of the text box.
    • Change the two constants in the command button 'OnClick' event.

    Once you have completed these steps, the new multiselect dialog will open immediately below the text box.