Append query from access to SharePoint

Hi,

Using SharePoint and Access 2013
When I run an append query from Access to SharePoint I get the message:

An INSERT INTO query cannot contain a multi-valued field.  

Any ideas on how can I handle this append.  I need to move data into SharePoint once.

Thanks,

Bill
LVL 14
Bill RossAsked:
Who is Participating?
 
Bill RossConnect With a Mentor Author Commented:
Hello,

I solved this issue by adding the Access table PK to the SharePoint list and omitting the multi-value fields in the insert query.  I then created a correlation table to match the SharePoint ID to the Access PK and ran multiple inserts to add the values into the SharePoint multivaluefield.value.

Thanks,

Bill
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Multivalue fields are an Access speciality for Access only.

You will need to either exclude the multivalue field or redesign your table to have a subtable holding the multiple values, then export both tables to Sharepoint.

/gustav
0
 
Bill RossAuthor Commented:
Hi,

The multi-value set up in SharePoint matches the look up table/detail table in Access.  The SharePoint design mirrors the Access table design.  I need a way to append records to the multi-value SharePoint column - not create the tables - as the data comes from multiple identical Access databases with same structure but different data.

Thanks,

Bill
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Gustav BrockCIOCommented:
I see.
Then, I guess, you will have to use VBA to open two recordsets of DAO.Recordset2 and copy records from the one to the other.
It seems:

    https://msdn.microsoft.com/en-us/library/office/ff197737(v=office.15).aspx

that the multivalue fields must be handled by the option of Recordset2 (missing in Recordset) to handle the multivalue field with the ParentRecordset property:

    https://msdn.microsoft.com/en-us/library/office/ff196492(v=office.15).aspx

I have no Sharepoint tables to test with but it seems to be the route to follow.

/gustav
0
 
Gustav BrockCIOCommented:
Thanks for the feedback.

/gustav
0
 
Bill RossAuthor Commented:
Solved it myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.