locked
Cannot update a SharePoint list from MS Access 2010 RRS feed

  • Question

  • I have a query in MS Access 2010 to update a SharePoint list that I have linked. However, it will not update giving the following error message:

    'You cannot reference rows created when you are disconnected from the server because this violates the lookup settings defined for this table or list. Please reconnect all tables with the server and try again.'

    The two tables referenced by this query are a native Access table and a linked SharePoint list - I am updating values in SharePoint from values held in the native Access table. I can read the SharePoint list in Access and can read and update the native Access table. I can also update the SharePoint list from SharePoint.

    I tried to delete the linked table and recreate it but this did not work.

    Any ideas?

    Regards

    Rory


    Regards Rory

    Monday, July 2, 2012 11:33 AM

Answers

  • Hi Steve,

    Thanks for your response but I have resolved this issue. The error message was actually accurate! It was a data integrity issue. What threw me was the fact that the data I was putting into the table was not breaking the list's integrety rules - it was existing data in other columns that I was not updating in the rows I was updateing.

    What I did not realise was that in SharePoint (unlike Access) you can change integrity rules even though existing data fails the new rules. I changed the rules to a Choice field removing one of the valid values but some records had the old value still. Also I had some 'People or groups' fields that reference SharePoint's user list. My IT department removes leavers from this list and this also caused an integrity exception.

    After cleaning up the existing data the problem was resolved. It looks like I am going to have to have some ongoing data checks to ensure that this does not happen again.

    Ken - sorry to hear you are having the same problem. I hope my experience helps track down your issue.

    Rory


    Regards Rory

    • Marked as answer by RoryDialogue Tuesday, July 24, 2012 8:15 AM
    Tuesday, July 24, 2012 8:15 AM
  • UtterAccess.com is a better Access Specific forum. 

    Other than that, check out these hits and see what applies to your situation:

    http://www.google.com/search?q=because+this+violates+the+lookup+settings+defined+for+this+table


    Steve Clark, MCTS | Twin-Soft.com

    Tuesday, July 3, 2012 3:56 PM

All replies

  • UtterAccess.com is a better Access Specific forum. 

    Other than that, check out these hits and see what applies to your situation:

    http://www.google.com/search?q=because+this+violates+the+lookup+settings+defined+for+this+table


    Steve Clark, MCTS | Twin-Soft.com

    Tuesday, July 3, 2012 3:56 PM
  • I'm having the same problem.  I have an MS Access 2010 Database that communicates through a link to a SharePoint 2007 List.

    On some records (or "items") I'm able to update through my form with no issue.

    On others (for whatever reason) I'm getting this error:  "'You cannot reference rows created when you are disconnected from the server because this violates the lookup settings defined for this table or list. Please reconnect all tables with the server and try again"

    Since I can update other items, I know that I have a good connection.  I'm trying to compare items but I don't see where I've made any data violations.

    Any ideas?

    Thursday, July 19, 2012 3:49 PM
  • Access was never designed for the web. So, if I had to quess, I'd go with the fact that there isn't a direct connection and Access suffers separation anxiety. (My dog eats the remote when this happens, so I think you're getting the good end.)

    Try this:

    1. From Access, pull up a record that you know you can, then close it.
    2. Unplug the network cable from the NIC card and try to pull up the same record.

    Do you get the same error?


    Steve Clark, MCTS | Twin-Soft.com & Easy Bins Dumpster Rentals of Northern VA. (Specializing in driveway-sized roll-off dumpsters.)

    Monday, July 23, 2012 1:45 PM
  • Hi Steve,

    Thanks for your response but I have resolved this issue. The error message was actually accurate! It was a data integrity issue. What threw me was the fact that the data I was putting into the table was not breaking the list's integrety rules - it was existing data in other columns that I was not updating in the rows I was updateing.

    What I did not realise was that in SharePoint (unlike Access) you can change integrity rules even though existing data fails the new rules. I changed the rules to a Choice field removing one of the valid values but some records had the old value still. Also I had some 'People or groups' fields that reference SharePoint's user list. My IT department removes leavers from this list and this also caused an integrity exception.

    After cleaning up the existing data the problem was resolved. It looks like I am going to have to have some ongoing data checks to ensure that this does not happen again.

    Ken - sorry to hear you are having the same problem. I hope my experience helps track down your issue.

    Rory


    Regards Rory

    • Marked as answer by RoryDialogue Tuesday, July 24, 2012 8:15 AM
    Tuesday, July 24, 2012 8:15 AM