Solved

Appending Records to a Table with a Multi-value Field

Posted on 2014-02-24
12
2,804 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 34

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
 
LVL 34

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 34

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 34

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now