Data update error with SharePoint 2010 and MS Access 2010
I've encountered a strange error when attempting to update a SharePoint 2010 list that I have linked to via Microsoft Access 2010.
Error: Data cannot be inserted because there is no matching record.
This occurs in 2 scenarios:
- I attempt to run any UPDATE query against the list in MS Access
- I attempt to update a record from the list if the list view is filtered
The second item might need an explanation. If I simply open the linked list in Access, scroll down to a record I want to edit, and edit it, it works. If I filter that view first (for example, showing only records with a checkbox field checked), I cannot edit any records and get the error.
This only happens in one particular environment; others work fine with either approach. I've checked permissions (I have full control of the list, I am a Site Collection Administrator, etc.). I have tried linking to the list in various ways: from within Access, from the "Open with Access" ribbon button in SharePoint. I've deleted and recreated the Access DB file... no luck.
Also, Google has no knowledge of this particular error: searches for the exact error text come up with 0 results.
Any idea what to check? Running SQL-style queries against this SharePoint list is the only viable option for maintaining it.
Answer
I determined the problem. As I was manually editing some more records in the "unfiltered" view of my SharePoint list, I found that the occasional record would still not update and throw the same error.
I happened to notice the records that would not update had another column set, which was a lookup column (you're right, Remou). But oddly, the ID value was displaying and not the lookup value. This didn't make sense, as MS Access had also linked all related lists.
I opened up the appropriate lookup list only to find Access was not retrieving any of the values. This again was odd, because the values existed in SharePoint, and the lookup relationship was intact were you to edit the same record in SharePoint. I couldn't force Access to update or fix its connection to the list.
I ended up deleting the lookup SharePoint list and recreating it. This solved the MS Access problem.