Solved

Append query from access to SharePoint

Posted on 2015-02-02
6
314 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 50

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 50

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 50

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

717 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