“Data cannot be inserted because there is no matching record” error

Microsoft Access and linked tables to SharePoint lists are commonly used to manipulate data from SharePoint sites. Through the Microsoft queries, users can bulk update or insert data into a SharePoint list. I also did the same, but a few days ago I started to receive an error running an update query against a SharePoint list: “Data cannot be inserted because there is no matching record”.

In my case, removing some users from the site was the cause of this. People/group field type is actually a type of lookup field which is pointing to User Information List. But when you remove a user from site, which means removing it from the users list also, people/group field value is not reset to blank and it keeps continue storing the ID of removed user. So, when you try to run an update query you actually force the field to keep the value, but it fails because users is not anymore accessible in users list. If you recently removed users, go to your list and update records replacing removed users with other users, which are not removed from your site.