Why is a Required Field in SharePoint List not reflected in the Linked table in MS Access?

Question

I have recently tried to migrate my backend MS Access database to SharePoint as lists. This seems to have worked and I've now relinked my MS Access front end to the SharePoint lists. However, I found what looks like an anomaly. In the SharePoint list, I' have set certain fields to be 'Required', but when I look at the same field via the MS Access the field is not set to 'Required' - see the screenshot below as an example:

Please can someone help me to resolve this, because I have forms in the MS Access front end that need to know whether or not a table's field is 'Required' or not.

Answer 1

Hi Paul,

Good that you have a workaround.

The Microsoft Access team reacted quickly and created a change that ensures that the Sharepoint properties like Required are displayed independently of the cache settings. It will take some time (betas etc.) until it arrives in the Current Channel.

The LTSC versions by default only get rather important fixes and rarely other changes. It is still under discussion, but there is a chance that this change will also come to LTSC.

Servus
Karl
****************
Access Forever, News, DevCon
Access-Entwickler-Konferenz AEK

Answer 2

Hi Karl

Really sorry, but that was done as part of the migration (I think I read it somewhere because the SharePoint lists wheren't accepting updates from the Front End.). I've got Use the cache format that is compatible with Microsoft Access 2010 and later ticked, and Never Cache ticked too.

Answer 3

Hi Paul,

In my tests checking the option has corrected the display of the Required status.

When I tick the option and Never Cache, then open the linked table in datasheet view, click into a field/column that is set to Required and choose Table Fields in the ribbon, I see the check box for Required ticked in the ribbon and grayed out=deactivated like almost all properties.

When I untick Never Cache then the ribbon items in datasheet view are activated and I can toggle the Required property there.

What do you see in datasheet view and can you toggle the Required property as described?

Servus
Karl
****************
Access Forever, News, DevCon
Access-Entwickler-Konferenz AEK

Answer 4

Hi Karl

So I think we're making some progress. If the 'Never Cache' option is ticked, I see no change in behaviour - i.e. the 'Required' fields are still unticked in the datasheet view when viewing the 'Table Fields' in the ribbon. If I untick the 'Never Cache' option, the 'Required' fields (that I've checked) are showing correctly in the datasheet view via the 'Table Fields' ribbon. However, the the 'Design view' still has the 'Required' attribute set to 'False' for the same fields. I've done some more testing, and it seems that if I untick the 'Never Cache' option, all the tables I open will have their appropriate 'Required' fields set correctly. Unopened tables do not. If I then re-tick the 'Never Cache' option, those previously opened tables retain the correct 'Required' setting.

It would seem that the answer is the untick the 'Never Cache' option, open all the tables, and then re-tick the option. I have no idea why this works (or what behaviour would change if I left the 'Never Cache' optiton unticked), but it gets me the functionality I need to continue the development, so thank you.

Answer 5

Thank you Karl.