Tuesday, April 8, 2014

Data cannot be inserted because there is no matching record - Sharepoint / Access List Error

Occasionally, my SharePoint 2010 list, which is attached in a MS Access 2010 database will refuse to allow me to run an update query on the list.

The Access error I get when running the update query is “Data cannot be inserted because there is no matching record".

An internet search lead me to some clues, but none of the responses directly applied to my situation.

I did find an answer from these clues and added my solution here: http://stackoverflow.com/questions/3791372/data-update-error-with-sharepoint-2010-and-ms-access-2010

In my case, my attached list is called “ProjectSearch” and running an update query on the list produced this error.

I observed that my database had an additional list that was automatically attached for me when I attached to my “ProjectSearch” list. This is not a list that I see in SharePoint – but it is an important relationship in Ms Access to the list.  I found that reading more about SharePoint to Access lists helped http://office.microsoft.com/en-us/access-help/import-from-or-link-to-a-sharepoint-list-HA001230313.aspx

To resolve my list error, I simply needed to refresh this secondary additional list. Then I was able to run my update query without an issue.

1 comment:

  1. Many thanks for this. I've been having this issue for weeks, and it's been getting worse recently.

    I never even realised I had an additional lookup table (in my case it was SharePoint UserData). Seems if I delete this from Access, close the DB then reopen it, that table gets recreated with all the users necessary, and it runs without a hitch! Many thanks. I owe you a drink!


Designed ByBlogger Templates