Cannot update Sharepoint list

[CedarTree]

Hello - trying to run an update query on a linked Sharepoint list in Access. I can manually change SOME of the rows, but not all (get a valid values/data integrity error). I'm not aware of any validations within my list, the field I'm updating is a text field, and I can't see any obvious difference between the rows I can edit versus the one's I can't. Please help!!! Thanks!!!

 

[MajP]

Can you provide the specific error message (Screen shot)? Does the existing list have any lookup columns?
Can you screen capture the properties on that field.

 

[CedarTree]

Thanks. All the same options as you have above (i.e., no requirements that the field be filled in, or unique values). No lookup fields. Here's the error:

 

[CedarTree]

Ha - turns out it wasn't a lookup field, but an unrelated choice field that was causing the issue.

 

[MajP]

Does the existing list have any lookup columns?

My first guess because these are a real PITA.

 

[GPGeorge]

Ha - turns out it wasn't a lookup field, but an unrelated choice field that was causing the issue.

Potentially irritating comment: SharePoint (and Dataverse) refer to these as "Choice" columns, whereas in Access, the term applied is usually "Lookup".

 

[MajP]

Potentially irritating comment: SharePoint (and Dataverse) refer to these as "Choice" columns, whereas in Access, the term applied is usually "Lookup".

Wrong. Sharepoint refers to both Lookup and Choice. Apples and oranges. Showing both choice columns and lookups.

 

[GPGeorge]

Wrong. Sharepoint refers to both Lookup and Choice. Apples and oranges. Showing both choice columns and lookups.
View attachment 117732

Thank you for the graceful correction.

 

[Isaac]

Hello - trying to run an update query on a linked Sharepoint list in Access. I can manually change SOME of the rows, but not all (get a valid values/data integrity error). I'm not aware of any validations within my list, the field I'm updating is a text field, and I can't see any obvious difference between the rows I can edit versus the one's I can't. Please help!!! Thanks!!!

Are there calculated columns anywhere on the row (not just what you're trying to update), are their lookup or People fields anywhere in the row, and do you have any Views based on this sharepoint table that have those.

 

[Isaac]

Sharepoint lists with Access in general is a real PITA.

If by any small chance you have the Time, Inclination, and corporate Charter to do so ... Look into making Power Apps with Sharepoint Lists instead.

 

[MajP]

By any small chance you have the Time, Inclination, and corporate Charter to do so ... Look into making Power Apps with Sharepoint Lists instead.

I am going this route now. Learning as I go.

 

[Isaac]

that's awesome - hey have you tried Power Automate yet? there is Desktop, but I'm thinking on a corporate account/server.
I made a few automate Flows before changing jobs and it was pretty cool. very good for updating stuff user-less, like ssis

 

[MajP]

Power Automate

A little. That is like a crazy enigma wrapped in a riddle. It is like someone explaining to me how bit coin works and after a while I am like "are you making this up? This cannot be real."

 

[lexkinter]

@MajP and @Isaac I saw you say that SP Lists are a PITA and am struggling to update a list, either from Access or from PowerAutomate. I attached image of Access error (... can't update .. data integrity rule...), raw data (excel file exported to use in PowerAutomate), and PowerAutomate errors. Can you offer any advice, please? Thank you

 

[GPGeorge]

The error message tells you the problem, in less than obvious terms.

I can speculate about the problem a bit, but this would be subject to your verification about the field in question.

In SharePoint lists, the concept of Primary and Foreign Key constraints is dissimilar to standard relational databases. Rather, in order to enforce Referential Integrity between two tables, you have to use Lookup Fields. This is the same Lookup field most Access developers are loath to implement in Access. Unfortunately, the only way to achieve the equivalent functionality of Primary/Foreign Key constraints is through the Lookup field mechanism.

What can, and often does, go wrong here is that inexperienced Access users implement Lookup fields wrong. That's one of the arguments against using them in standard Access tables, by the way. Not that they don't "work" as advertised, but that it is all too easy to get it wrong and create insurmountable hurdles for users.

The problem is, most likely, that instead of saving the Primary Key from the related table as the Foreign Key in the table shown in your screen shot, your Lookup field and the data entry for it are trying to save the text value from the lookup. And that can't be done. Unfortunately, we've seen that particular misstep many times over the years.

I'm guessing your Power Automate problem is similar, although that's outside my direct experience and knowledge.

 

[lexkinter]

The error message tells you the problem, in less than obvious terms.

I can speculate about the problem a bit, but this would be subject to your verification about the field in question.

In SharePoint lists, the concept of Primary and Foreign Key constraints is dissimilar to standard relational databases. Rather, in order to enforce Referential Integrity between two tables, you have to use Lookup Fields. This is the same Lookup field most Access developers are loath to implement in Access. Unfortunately, the only way to achieve the equivalent functionality of Primary/Foreign Key constraints is through the Lookup field mechanism.

What can, and often does, go wrong here is that inexperienced Access users implement Lookup fields wrong. That's one of the arguments against using them in standard Access tables, by the way. Not that they don't "work" as advertised, but that it is all too easy to get it wrong and create insurmountable hurdles for users.

The problem is, most likely, that instead of saving the Primary Key from the related table as the Foreign Key in the table shown in your screen shot, your Lookup field and the data entry for it are trying to save the text value from the lookup. And that can't be done. Unfortunately, we've seen that particular misstep many times over the years.

I'm guessing your Power Automate problem is similar, although that's outside my direct experience and knowledge.

Well hello again, George! I always get a smile when you reply, as I've been lucky to get your responses for so many years. The list has no formatting or validation or required fields, but does have one Lookup. I'm not trying to write to the lookup, just create a record. Screenshot to list attached below and SQL below as well. Any other thoughts? Thanks

INSERT INTO
    Issues1 (
        Title,
        Description,
        [Opened Date],
        JobNumber,
        [Action],
        ConstBusTeamSelectedForAction,
        CONTID
    )
SELECT
    [COPY Issues 0425251].Title,
    [COPY Issues 0425251].Description,
    [COPY Issues 0425251].[Opened Date],
    [COPY Issues 0425251].JobNumber,
    [COPY Issues 0425251].Action,
    [COPY Issues 0425251].ConstBusTeamSelectedForAction,
    [COPY Issues 0425251].CONTID
FROM
    [COPY Issues 0425251]
WHERE
    (
        (
            ([COPY Issues 0425251].JobNumber) = 124103
            OR ([COPY Issues 0425251].JobNumber) = 132102
        )
    );

 

[MajP]

I would try testing it field by field. See if you can just add the title. If that works add another field. If that fails try adding a different field. I would think this would work even with a lookup in the table.

I will tell you the default fields "Title" and "Description" always seem to be problematic. I always avoid them and add my own like "JobTitle and JobDescription". For example I do not think you can make these default fields required, so somehow they are treated differently.

 

[GPGeorge]

I would try testing it field by field. See if you can just add the title. If that works add another field. If that fails try adding a different field. I would think this would work even with a lookup in the table.

I will tell you the default fields "Title" and "Description" always seem to be problematic. I always avoid them and add my own like "JobTitle and JobDescription". For example I do not think you can make these default fields required, so somehow they are treated differently.

I agree on the naming convention for those fields. In fact, my first instinct was to double-check that one or both might be meta-data fields added by SharePoint to tables. It does include a number of such fields that are superfluous in Access.

It may also be that the Lookup field, AffectedWorkArea, is a required field. If it is and because your code doesn't insert a value in it, that would conform with the error message. It refers to data integrity rules, and a missing required foreign key would fill that bill.

I would try testing as Pete suggests, field by field.

 

[lexkinter]

@MajP and @GPGeorge Thank you so much! I'll give it a try. Sorry to see UtterAccess go away, but happy that you're still out there! BTW, I'm coming to Washington in a couple weeks. I hope weather is nice

 

[GPGeorge]

@MajP and @GPGeorge Thank you so much! I'll give it a try. Sorry to see UtterAccess go away, but happy that you're still out there! BTW, I'm coming to Washington in a couple weeks. I hope weather is nice

Thank you. We will all miss UA.

Right now, we have summer weather in Western Washington. In two weeks? Anybody's guess.