CODEWITHSUNDEEP
validationms-accesssharepointsharepoint-2010
jdg
jdg

Reputation: 547

Access to SharePoint Update Query - "Value Is Not Valid Or Would Break Data Integrity Rule"

In Microsoft Access, we have a local table and a linked table to a SharePoint list.

The below update query: 

Update Linked 
INNER JOIN Local on Linked.ID = Local.ID
Set Linked.TEXT_FIELD = Local.TEXT_FIELD
, Linked.CHOICE_FIELD = Local.TEXT_FIELD
, Linked.INT_FIELD = Local.INT_FIELD
...etc

Gives us the error:

You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule. Please correct and try again.

Researching elsewhere we find that the error occurs when choices are changed in a restricted lookup field. We have scoured the list looking for those changes, to no avail. Furthermore, while choices in some fields may have changed, all of the choices in our update are part of the original list of valid entries. In sum: all of the values in all of the records we are trying to update are valid choices on the destination list.

We have attempted to debug the code to isolate the offending field, but get the same error on even single field updates. The errors occur even in updates of individual fields that do not have valid choices, as in: 

Update Linked 
INNER JOIN Local on Linked.ID = Local.ID
Set Linked.INT_Field = Local.INT_Field

We have attempted to re-link the SharePoint list. We have also deleted the linked list and manually re-added.

Needless to say, this has been very frustrating. Any guidance you can provide would be greatly appreciated.

Upvotes: 1

Views: 8695

Answers (5)

KaskStack
KaskStack

Reputation: 1

I received the same error and after scratching my head numerous times, I found out that the choice column that I recently added to SharePoint with a calculated value for Default value was the culprit. After removing it and put back to Choice with blank value, Access was able to append data again.

Upvotes: 0

Jens Munk
Jens Munk

Reputation: 21

I found the solution for my part; it turns out that setting up the lists on Sharepoint had created a lot of columns with names as existing columns, preceded by underscore.

I deleted those, and it worked again.

Hope this helps.

Upvotes: 0

Epistemic Data
Epistemic Data

Reputation: 19

This is probably the first time I've had an answer in which I've had 100% confidence. The error is indeed thrown when one attempts to enter data into a field for which standards have been created. For instance, if you have set options for the field type "Choice", and entries you attempt to use do not fit any of those options. But the key here, and the reason why I'm adding to the answers is that the field throwing the error doesn't have to be the one on which you might be focused. It could be any field. So check all the fields that are a part of the rows you are entering, not just the one you may be in your current focus.

Upvotes: 0

Prakash Arumugam
Prakash Arumugam

Reputation: 1

I am using MS office 2019. I tried changing the filed with data type "short text" to "Long text". It worked for me.

Upvotes: 0

r-q
r-q

Reputation: 63

In my experience, this also happens when other fields in the rows you are trying to update contain values not allowed in the "Choice" fields, or even when these fields reference the old (deleted) values. You can see that when you open the database file in Access and click through those fields.

The root cause is usually deleting items in a Choice field on SharePoint online.

The solution that worked for me was to re-create the Access database linked to SharePoint.

Upvotes: 1

Related Questions