MVF and Attachment Fields

I had originally created this demo for the book "Professional Access 2013 Programming." I would like to thank the authors and publisher for giving me the permission to repost it here because I want to make it available to anyone who could benefit from it.

Multi-Value Fields (MVF) and Attachment Fields were introduced in Access 2007. MVFs allow for multiple values to be selected and stored for each record. MVFs are created by going to the Lookup tab in the Field Properties section while viewing the table in Design View and selecting either Combo Box or List Box in the field's Display Control property and then setting the Allow Multiple Values property to "Yes". The Attachment field is a special type of MVF, and it is Microsoft's answer for a better OLE Object data type field. It allows the user to store multiple external data such as images or other files in the table with a lesser impact on the database file's size through the use of higher compression ratio as compared to OLE Object fields. Attachment fields are created by selecting "Attachment" as the field's Data Type.

On the surface, using MVFs (storing multiple values in one field) appears to violate Normalization rules. However, the Access Team implemented the MVF functionality using a hidden, internal table to store the selections as "child" records to comply with Normalization rules. Attachment fields are implemented in the same way. As a result, handling MVF and Attachment fields requires additional effort on the developer in understanding their internal structure.

This demo illustrates how to use VBA code to copy or transfer an MVF or Attachment field from one table into another. This approach is necessary because using a regular Append or Make-Table query does not work with MVF and Attachment fields.

Please let me know if you have any questions. Or better yet, you can buy the book "Professional Access 2013 Programming" and jump straight to Chapter 17 (pages 663-667) for a more detailed explanation about this demo.

Applicable Versions

This demo was created using Access 2007 and tested using Access 2007, 2010, and 2013.

Bug Report

Please report all bugs to theDBguy@gmail.com.