Solved

Appending Records to a Table with a Multi-value Field

Posted on 2014-02-24
12
2,929 Views
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.
0
Comment
Question by:jdancel
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:jdancel
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?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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:

http://msdn.microsoft.com/en-us/library/office/ff821054.aspx

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.

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 35

Accepted Solution

by:
PatHartman earned 250 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.
0
 

Author Comment

by:jdancel
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.
0
 
LVL 92

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.
0
 

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.
0
 
LVL 84
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.
0
 

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?
0
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 
LVL 84
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).
0
 
LVL 35

Expert Comment

by:PatHartman
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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