How to Fix Access Connection Issues with SharePoint?

Microsoft Access can be connected with a wide range of data sources to share data. Many businesses with Access databases connect with SharePoint to import, link, or move data between them. This allows users to view and edit data in both applications. Businesses can use Access forms and queries on the front end and interact with data in SharePoint sites. Additionally, Access can be used to work offline with the data that is available in SharePoint lists. It is also more efficient to use Access for bulk editing. In this article, we will focus on fixing connection issues between Access and SharePoint.

How to Resolve Access SharePoint Connection Issues?

Connection issues between SharePoint and Access arise when the linked table in Access does not have the credentials for the SharePoint site that contains the list. These credentials could either be stored in the Windows Credential Manager or be a persistent cookie in Internet Explorer. When there is a connection issue between Access and SharePoint, users often notice that the Microsoft Access desktop database stops working. The Access database might freeze while trying to open a table that is linked to a SharePoint list.

Here are some of the workarounds to resolve Access SharePoint connection issues.

Update Registry Entry

• Open the Registry Editor, and search for the following registry subkey
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\xx.x\List
  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\xx.x\List
• Navigate to Edit>New>DWORD Value, and search for EnableLegacyListAuth.
• Right-click on the EnableLegacyListAuth search result and click on Modify.
• Type 1 in the Value data box and click on OK.
• Look for the HKCU\SOFTWARE\Microsoft\Office\xx.x\Common\Identity registry key.
• Ensure that the value for EnableADAL is 0, and then exit the Registry Editor.

Use the Refresh List Option

• Launch the Windows Task Manager, select Microsoft Access, and click on End Task.
• Restart the Access database.
• Right-click the linked table in Access. Expand More Options, and then click on Refresh List.
• Use the user credentials to log in again.

Programmatic Relinking

• Launch the Windows Task Manager, select Microsoft Access, and click on End Task.
• After restarting the database, create a new module.
• Use the RefreshLink method of the TableDefs collection to relink the linked Access tables with the SharePoint list. Note that the RefreshLink method has to be called only once for any one table if there are several linked SharePoint lists.
• Then call the TableRelinkSample()function. This can be done from startup code or the AutoExec macro in the database.

Apps4Rent Can Help with Access and SharePoint Hosting

Although it is possible to save an Access database file in OneDrive or SharePoint document library, it is not recommended to do so, as multiple copies of the database may get created resulting in unexpected behaviors if the files are downloaded locally for editing and then uploaded again. The better option is to use it on a single system so that changes are centralized.

As a Microsoft Gold Partner in the Cloud Platform specialization and a Tier 1 CSP, Apps4Rent can host Access and SharePoint on Azure, or our top tier SSAE-16 datacenters with reliable backup and support. Reach out to our Microsoft-certified consultants, available 24/7 via phone, chat, and email for assistance with Access and SharePoint.