none
Updating Sharepoint 2013 list (multi-value field) linked to Access RRS feed

  • Question

  • Hello, I have vba code that links to a SharePoint list and updates records in the list via SQL update statements.  I have been using this code successfully when linking to a list in our SharePoint 2010 environment.  We are migrating to SharePoint 2013.  I have updated my code to point to the new environment.  In the new environment, if I try to write a string that is longer than 256 characters to a certain field, I get an error that says "Syntax error in string in query expression".  When I open design view on the linked table (the SharePoint list), it shows that the data type for the field I am trying to write to is "Long Text".  The underlying SharePoint field is a multi-value field.  Also, when I try to run this update query manually within access, I get the error message ("Syntax error in string in query expression") and then after that, the error window shows the string I am trying to write but it seems to cut off before the end of the string, and then it ends in what appear to be Chinese characters. 

    All of this worked fine in SharePoint 2010.  There were no issues writing longer strings to the same field in the 2010 environment. I would appreciate any insight in to why this might be happening.

    Thanks

    Friday, June 30, 2017 9:24 PM

All replies

  • Hello,

    What is your Access version? If you insert the string in SharePoint list, would you get any error? If it doesnt work, i think the issue causes from your sharepoint list design. If it works, please try to link the SP list manually in Access and update the field to check if your VBA code casues the issue.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 4, 2017 8:47 AM
    Moderator
  • Hi Celeste,

    I am using Access 2013 (part of Office Professional Plus 2013).  I can successfully insert the string in the SharePoint list directly without any errors.  I linked the list manually in Access, and I still had the same error when trying to update it via SQL.

    Thanks for your help.

    Wednesday, July 5, 2017 1:04 PM
  • Hello

    If you update manually, would you get any error?

    If there is no error, I suggest you share your query here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 6, 2017 7:39 AM
    Moderator
  • I am able to update the field manually with no error.

    Here is the query (modified to make it anonymous):

    Update [Accounts] SET [Comments]="6/30/2017 - Per Tracking System, ASD moved from 7/31/2017 to 10/31/2017, value updated from 10,000,000.00 to 19,074,085.00, Margin updated from 0.00% to 0.00%, Stage updated from Terms and Conditions to Won, Confidence Factor updated from 80% to 100%, Won-Lost Reason updated to Technical Solution" WHERE [ID]="xxxxxxxxxxxxxxxxxxxxxxx"

    The issue appears to be with the length of the string the query is trying to write to the "Comments" field.  Shorter strings seem to write with no error.  All updates where the strings are longer (than 256 characters) fail.

    Thursday, July 6, 2017 4:29 PM
  • Hello,

    How do you create the multi-value field and change its bound fields in sharepoint list?

    When i creating a lookup field in sharepoint list, it would always be bound to ID field, so the field is Number in my linked table.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 7, 2017 10:38 AM
    Moderator