• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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
0
Bill Ross
Asked:
Bill Ross
  • 3
  • 3
2 Solutions
 
Gustav BrockCIOCommented:
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Bill RossAuthor 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 BrockCIOCommented:
Thanks for the feedback.

/gustav
0
 
Bill RossAuthor Commented:
Solved it myself
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now