Data update error with SharePoint 2010 and MS Access 2010

Question

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:

1. I attempt to run any UPDATE query against the list in MS Access
2. 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.

Solution 1

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.

Solution 2

I experienced the same symptoms - meaning that any Update query to a particular SharePoint list from MS Access 2010 failed with this error - but my fix was different. The solution above did get me looking in the right direction, however.

In my case, I had no user-defined lookup fields in the list. When you link to a list, however, there is an implicit lookup table called UserInfo that is also created in Access when the linked table is created. When I examined this UserInfo table, it contained only a single record and it should have contained dozens.

My fix was to right-click this table in Access, More Options..., Relink Lists...

I entered the existing SharePoint site and lists as if they were new ones. The UserInfo table now contained the appropriate number of records and the error on Update ceased.

Comments

[David-W-Fenton]

Deleting LOOKUPs is always a good idea. The feature should not exist, because it's mixing a UI component in with table design.

[Aaron Kempf]

totally totally totally disagree. LOOKUPs are like LITERALLY the only thing I love about Access.

[Sally]

I hit this again today. Now I'm using Access 2016 and SP 2010. Refreshing the lists worked for me this time. (I do have lookup lists and was trying to concatenate two values and put into a third column.)