Appending Records to a Table with a Multi-value Field

Posted on 2014-02-24
Medium Priority
Last Modified: 2016-09-13
I need an example of an INSERT SQL statement that would allow me to add a record to a table that has 12 different fields, one of which is a multi-value field that can contain up to 10 values.
Question by:jdancel
  • 4
  • 3
  • 2
  • +2
LVL 41

Expert Comment

ID: 39883770
If it is coming from another table, use the QBE to build the append query.  That should get you the syntax you need.  

I don't have any examples because I would NEVER, under any conditions use a multi-value field.

Author Comment

ID: 39883838
Why such a fervent response against a multi-value field?  I was trying to write to some of the newer functionality like multi-value fields.  What do you see as the downside to using multi-value fields?
LVL 86

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39883869
I think you'll find most professional developers have no use for Multivalue Fields, or Attachment fields, or most of the other new features of Access.

MVFs were created mostly to provide new UI functionality, and that's always a bad thing (at least in my opinion). They won't upsize to any other database (and the new versions of Access are using SQL Server, so don't bet the farm on MVFs just yet). They're difficult to work with (see the issue you're having here), and in general just clog up the works.

I'm not sure you can use a single INSERT statement to put values directly into a MVF. You can use DAO and VBA to do it:

Here's some information on working with MVFs in queries:


As you can see, you'd first open a Recordset for the "main" record, and then open a second recordset to get to the MVF tables.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 41

Accepted Solution

PatHartman earned 1000 total points
ID: 39883886
The applications I create are frequently upsized to SQL Server or some other RDBMS and multi-value fields cannot be ported.  They also can't be used in A2013 web apps because the BE there would be SQL Azure.

The real objection is how they were implemented.  Behind the scenes they are correctly normalized tables in that the multi-value part is actually stored in a separate table as it should be.  The problem is that because of the way they are implemented (MS hid the many-side table so you can't see it), you can't use them for existing relationships and you have to learn a completely new syntax for SQL to work with them.

The multi-value field as implemented is simply a crutch for non-professional users who don't understand how to create a 1-many relationship.  If you know how to create such a relationship, you are far better off doing that.  The only positive thing about the multi-value fields is the cute control MS uses to display it.  If you create your own, you would use a subform instead of this cute control.

I would also avoid all the other new data types that were implemented with A2007.  None of them can upsize so they limit you to Access plus force you to learn new SQL syntax to work with them.

Author Comment

ID: 39884079
Thanks for the responses.  I'll just go back to using a 1-to-many relationship in the tables.  I know how to do it and was just exploring some of the functionality.  I hadn't thought about the repercussions when it comes time to port this to SQL Server, which is likely.  Thanks for the heads-up.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39884092
100% agreed with the Experts above.  In my opinion, MVFs should never be used, period.

If you think you need a MVF, then what you really need is a separate, one-to-many related table.

Expert Comment

by:Christian Vonäsch
ID: 41795754
MVF are great to use in Access! And I AM an Access Professional (for over 20 years). MVF are perfectly implemented as separate n:m-Tables in the background. You can of course do the same manually. BUT:
1. you have to create a separate subform for every MVF which is annoying for one-field only m:n-Tables, the ListBox or ComboBox Implementation is so cute in my opinion.
2. to programm the genious standard filter-functionality for MVFs you have to do an enormeous effort!

The non-compatibility of MVF with SQL-Server is very true. But I decide to solve a customers request for a tool first. If Access (with Jet-Engine) is good enough, then I love to use the advantages of MVFs. If SQL-Server is the best choice, I have to calculate a huge amount of development time to implement "simple" n:m-relations! I did program a smart MVF-replacement in an Access front-end that uses SQL-Server as back-end. And it's a BIG amount of work. (Solved it with unbound ListBoxes in PopUp-Forms that store the m:n-records in a separate m:n-table. Also filtering is hard developers work and you have to code SQL-statements like "where AddressID in (select AddressID from AddressGroupTbl where GroupID in (1,2,4,9))" to get addresses that belong to certain groups.

MVF are not simple to use, that's true as well. You have to use DAO to fill it by code. And to add MVF-fields to your Access-back-end by code is not easy either. But if you learned, how to handle it once, there's no problem for future projects.

So please don't tell the world, MVFs are bad, just because you did not manage to make them work for you and to handle them by VBA code. I hope, people stop to be so critical to MVFs and Microsoft will not decide to take out this helpfull feature out of Access!

Thanks for reading.
LVL 86
ID: 41795788
I think you'll find the overwhelming majority of professional Access developers have long since decided that MultiValued Fields are to be avoided, for various reasons (like the one that was discussed about 2 years ago in this closed question). There is no compelling reason to use an MVF, and there are many reasons to not use them (see above for one), including the ridiculous methods you must use to work with them through code. In my opinion (and in the opinion of most of the professional Access community), you're much better off handling 1 to Many relationships using time-tested, traditional methods.

Expert Comment

by:Christian Vonäsch
ID: 41796263
Thank you for your answer. Can I ask you to comment some of my points? You just repeated, that MultiValued Fields are useless.

Second: do you agree that for a small address-DB with group (i.e. Family, Friends, ...) that consists of two forms: One in datasheet view to display the list of adresses and to be able to filter by form by names, postal codes, ... AND groups. The other is opened if you double-click a line in the datasheet and it displays the details of the current address, with the possibility to check multiple groups, you have got half an hour of developing time. The same without MVF takes you a lot more.

What do you think?
LVL 41

Expert Comment

ID: 41796485
1. you have to create a separate subform for every MVF which is annoying for one-field only m:n-Tables,
I solved this problem back in the early 80's using COBOL with an IMS database.  When I adopted Access in the early 90's, this was my first mini-app and it has changed very little in the intervening 20+ years.  It uses a single self-referencing table so one of the "tables" is a "table of tables" or if you prefer, you can implement this as a two table solution with a table of tables and a table of contents.  I use a form with a subform and I have two reports.  So these objects are imported into all new applications and voila!  I have a table maintenance function that allows the user to manage his little list-type tables and I don't have to worry about them.  One of my latter modifications to the app was to add a column that indicated user level since some tables can be maintained by anyone, some need to be maintained by the administrator, and some can only be maintained by the programmer.

More complicated lookup tables get their own table and maintenance forms and MVF wouldn't work for these anyway.

If SQL-Server is the best choice, I have to calculate a huge amount of development time to implement "simple" n:m-relations!
Every one of my Access apps is developed using client/server techniques and therefore, it rarely takes me more than an hour to upsize to SQL Server.

MS could have chosen to implement MVF using visible tables but instead they chose to hide them and that forced a completely convoluted implementation for both code and SQL which INMNSHO is much harder to understand and work with for newcomers (and experts) than normal 1-m relationships.  And all we got for this is a "cute" control.  BTW, this control looks very much like the old listbox ActiveX control that was deprecated a couple of years ago.

MVF's actually came about to allow a tighter integration with SharePoint.
LVL 86
ID: 41796573
My entire point was that your suggestion to use MVFs is not shared by the Access developer community at large. Power users, and those who don't do this professionally, seem to like MVFs, since it allows them a quick and dirty way to group attributes, but those of us who have had to sort out those messes long ago stopped using them (or suggesting they be used).
LVL 41

Expert Comment

ID: 41796622
I think the point is quick and "dirty".  They are way too much trouble for people who work with queries and code and all you get in benefit is the "cute" control.  For people who develop for their own use, I can understand the attraction since they seem at first glance to be simple.  But even developing for myself, I wouldn't use one because who knows when it would rise up and bite me causing significantly more work than if i had done it my self from the beginning.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 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