INSERT INTO Multivalue field

[Peter Schmidt]

I have an Access 2007 table containing a number of columns including 1
multivalue field. I have a spreadsheet containing data that I wish to import
into this table with spreadsheet columns that correspond with the table
columns. The multivalue field column contains the values separated by a
semi-colon (.

When I try to add the data using an Append query I get an error meassgae
saying I can't INSERT INTO a multivalue field. What is the best way of
getting the data in this column? Please note, that in this instance I do not
want to follow the standard normalisation rules and create a junction table.

 

[Pete]

Thanks for the reponse. I was aware that the ACE engine created the junction
table as a hidden system table and managed it for you. I had presumed that it
managed the INSERTS too or that that there was a function I could call (or
write) that would do this. I will try substituting the ; for a , but I don't
think it will work - if you try and paste even a single value into a
multi-value field in table view it won't let you, although it will let you
copy the record above by pressing CTRL+' !

To be honest this is the first app I have built where I have used a
multi-value field - as a database developer of 20+ years experience it went
against the grain a bit and reading the comments from the MVPs confirms that
they are generally a bad idea! In this instance it made sense as it was just
recording a couple of 'Interests' against a Contact and I had built a list
view that the user could use to filter the multi-value field without a
problem.

It would be good if in the next version you could map the multi-value combo
to your own junction tables somehow as it is a neat form control and
potentially very useful... Only a couple of weeks until the 2010 Technical
preview!