Solved

Append query from access to SharePoint

Posted on 2015-02-02
6
272 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
  • 3
  • 3
6 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 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 49

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 49

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now