none
MS Access Error when running update query for SharePoint RRS feed

  • Question

  • Hi

    I've got a SharePoint list to manage project dates across a department.  I receive a consolidated Excel document once a month with updates to the project dates which is a linked Excel sheet to MS Access.  I've also linked the SharePoint list to MS Access and have a unique ID for every project which links the linked Excel data with SharePoint data.

    I use an update query to either populate missing dates or update existing dates that have changed.  Updating the missing dates works fine, however when updating the existing dates I get the following error: Data cannot be inserted because there is no matching record.

    I use the following SQL Code to run the update query:

    UPDATE [SP Planned Date] INNER JOIN [Project Tracker] ON [Excel Planned Dates].[Project ID] = [Project Tracker].[Project ID] SET [Project Tracker].[SP Planned Date] = [Excel Planned Dates]![XLS Planned Date]

    WHERE ((([Project Tracker].[SP Planned Date]) Is Null)) OR ((([Project Tracker].[SP Planned Date])<>[Excel Planned Dates]![XLS Planned Date]));

    Project Tracker = SharePoint List
    SP Planned Date = date field on SharePoint List
    Excel Planned Dates = Linked Excel file
    XLS Planned Date = date field in Excel file

    When running a Select Query there are 134 records where Project ID matches and I checked to ensure that the Excel file date formats matches the SharePoint date format.  I'm using SharePoint 2013 and MS Access 2010.

    Any ideas of why I'm getting the no matching record error?

    Thanks!

    Tuesday, November 6, 2018 6:33 PM

All replies

  • Hi,

    Based on your description, I will move your thread to Access forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, November 7, 2018 7:07 AM