Access and SharePoint Integration May Be Broken

Question

I am posting this here in the hopes that a Microsoft MVP or employee may see it, verify the issue, and report it to the right people at Microsoft.

We have a fairly significant LOB application with the front-end hosted in Access and the back-end hosted in SharePoint Online lists. This application has been working well for several years.

Sometime in the past few weeks (last known good date was June 23), a change in either Access or SharePoint (or maybe the Windows OneDrive sync client) seems to have broken integration between the products.

When the modern cache format is enabled (the default), all SharePoint calculated columns unexpectedly show errors, as shown here (in a fresh database where I imported just one list from our site as a test):

Naturally, this completely borks the application, with VB code throwing errors at startup.

Using the legacy cache format, or disabling caching altogether, seems to restore functionality--(though this would come at a cost to performance):

But there is a major caveat to this. Other functionality is apparently broken. We have straightforward update queries, for example, the hang indefinitely in these modes, rendering this an unacceptable work-around.

I have been able to replicate this on several different PCs. Also, on several different SharePoint sites in our tenant.

Currently, this is blocking work for us, and we are hoping to see it resolved as soon as possible. If anyone with knowledge of the right people at Microsoft to call attention to this, we would be grateful for any help.

I am using the Access forum because the last time I tried to get help from SharePoint Online support for an Access-related query issue using SP list data, they had no idea what I was talking about.

(Also, for Access database experts: please do not suggest that we avoid using calculated columns in SharePoint lists. This is a hybrid app with most of our users working with data strictly through SharePoint, and SharePoint views do not support the same features as queries do in Access. Making use of these SharePoint features are essential for these users.)

Our Access version: Microsoft® Access® for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20124) 64-bit

Thank you in advance for any help.

heinziat · Answer

I am neither MVP nor Microsoft employee, but there's one piece of advice which might be helpful to you. According to the build number, you currently use the Current Channel of Office. My recommendation is to try to reproduce the issue with the three Monthly Channel builds (here is a list of the build numbers, here is an instruction on how to change channels, and here is how to install a specific build).This has the following advantages:If this is a blocking issue and it doesn't occur in one of the monthly builds, your users can continue working.If it fails even on old builds, you know that the issue is (probably) not Access-side but Sharepoint-side.When the bug report reaches Microsoft, knowing when the bug was introduced will help them fix it.

george_hepworth · Answer

I can replicate the error. PubSort is a calculated column.Microsoft Access (Version 2607) Build 16.0.20228.20124 Current Channel 32-bitThe problem has been reported to Microsoft. It's before hours in Redmond at the time of the report. It will be a while before someone sees it.In the meantime, try rolling back the update. The links in HeinziAT's post will help.

Forum Discussion

Access and SharePoint Integration May Be Broken