[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Append query from access to SharePoint

Posted on 2015-02-02
6
Medium Priority
?
352 Views
Last Modified: 2015-03-03
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
Comment
Question by:Bill Ross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 40585642
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
 
LVL 14

Author Comment

by:Bill Ross
ID: 40585888
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40585954
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 14

Accepted Solution

by:
Bill Ross earned 0 total points
ID: 40622946
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40622968
Thanks for the feedback.

/gustav
0
 
LVL 14

Author Closing Comment

by:Bill Ross
ID: 40641403
Solved it myself
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question