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.

 

[June7 via AccessMonster.com]

Is this field a multivalue field as described by Access help? Here is an
excerpt:
"You might wonder why Office Access 2007 allows you to store more than one
value in a field, when most relational database management systems forbid
this. The answer is that the database engine in Office Access 2007 doesn't
actually store the values in a single field. Even though what you see and
work with appears to be a single field, the values are actually stored
independently and managed in hidden, system tables. The Access database
engine handles this for you, automatically separating the data and bringing
it back together again to surface the values in one field."
The help page also says the default delimiter is a comma. Did you create the
field and specify semi-colon as delimiter? Perhaps if you use a comma the
engine will be able to handle the Append.

 

[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!