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

What is the behaviour in practice? I would hazard a guess that the field definition in the back end will determine whether or not Null is permitted, and that the value of the Required property shown in the front end is immaterial. If this should not be the case then you can easily disallow Null in a bound control in a form by means of code in the form's BeforeUpdate event procedure, setting the return value of the procedure's Cancel argument to True if the control is Null.

Answer 3

Thanks Ken for the suggestion. As you said, the back end does reject the insert if a 'Required' field is not set. However, here's my challenge... The form I spoke of is used to do a dynamic import of data into one or more tables based on preconfigured metadata (i.e. imported data item 'x' goes into field 'a' of table 'j'). I want to be able to check that all required fields are available in the data and disallow the import if that's not the case. To do this, I'm dynamically looking at each field of the table to check its' 'Required' property. Whilst testing, I found that this property is always 'False'. I suppose I was wondering if I've done something wrong in the migration or in linking the front end to the SharePoint lists, or if this is a genuine anomaly.

Answer 4

Hi,

If the Sharepoint list's column property "Require that this column contains information” is set to Yes, then the Required property in the linked list in Access should correspond with Yes. If that's not the case I would first try to update the link with a right mouse click on the linked list in the navigation pane **** and More Options - Refresh List.

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

Answer 5

Hi Karl. Thanks for your suggestion. I've just Refreshed a couple of the tables that have the issue, and unfortunately there's no change, the 'Required' properties in both tables still says 'False', and I double-checked that the SharePoint lists both have those properties ticked as 'Required'.

I take it that this is unexpected behaviour?

Paul