An INSERT INTO query cannot contain multi-valued field. (1 Viewer)

i have table on sharepoint which i can not link to my access database so i have to import it. table on sharepoint is same as in access and i need table from sharepoint updated with my access. unfortunately this table has multi valued fields. when i import table from sharepoint it, the table gets renamed so if the original table is tablea the imported table becomes tablea1. im trying to update my table in access but then i get error "An INSERT INTO query cannot contain multi-valued field." if i remove multi valued fields from query, the query is working fine. does anyone have any ideas how i can get the values into my table in access? any help is greatly appreciated

for anyone looking for answer to this heres the solution
lets say you have 2 tables tblMyTable, tblImportedTable
tblImportedTable has multi value fields. create new table tblValues and add records one at a time of the multi values. so lets say tblImportedTable has field colors with records as "red" and "blue"
in tblValues add records "red" and "blue"
create regular query update or insert and link the fields as needed. between tblMyTable and tblImportedTable then left join tblValues on multi value "value" so it looks like tblImported.colors.value. Make sure that you select ".value" instead of just multi field, then just set "Update To:" field to tblValues.Colors and that should do the trick you will also have to code it little in order to show all values

Many developers would recommend avoiding multivalue fields in favour of a related table.

Multivalue fields are an Access-only feature that would have to be completely reconstructed to use with a server backend so it is often simpler to do it with related tables from the beginning.

i agree 100%, as much as i hate multi value fields, im afraid i have no choice in this matter. our company sharepoint site was designed by other ppl and i have no control over that, but i have to grab the info from our sharepoint site. in my database there are no multi value fields but for those who are in the same boat as me, its nice to know theres workaround

Hi - Need help on similar issue, i tried to understand your response however, could not get it due to my little experience into Access. Could You please help. I have a table Import which is Excel linked and Sharepoint Table as Calc.