MS Access 2016 using SharePoint lists as tables – Access crash issue

SharePoint Asked by GeoffE on December 19, 2020

We are using Access 2016 and SharePoint (our Office 365 package). I have built a 7 table Access database and made these tables linked to SharePoint lists (the tables in Access show as the yellow icons). This works well on my workstation and I can work on this Access database both online and offline (using the External Data-Web – Linked Lists – Work Offline icon)

I have given all users Admin rights to these linked lists on the SharePoint site

When the Access database file is copied to another workstation (another user) and then opened on that workstation Access just sits trying to open then fails, goes through the “trying to find an answer to the issue” and “trying to recover data” and then closes

Has anyone any ideas please?

One Answer

This is due to how Access authenticates access to Sharepoint; the credentials are cached locally, and by user. Access is attempting to connect to these external lists, but has no credentials to do so.

This is an educated guess, but I think Access is looking for credentials in a location that doesn't have any for the current user; this is going to be a common issue for shared frontends with external tables in Sharepoint. Why it doesn't ask for credentials, I have no idea. That seems like the logical use case for when credentials don't exist.

To resolve this, I used a modified process from here:

  1. Open Internet Options, either through Control Panel, or Internet Explorer. Note that Edge does not use these options, and changing settings for it will not affect this at all.
  2. Add your Sharepoint site to your Trusted Sites.
  3. Click Custom Level, and scroll all the way to the bottom. Choose, "Authenticate with current username and password".
  4. Click OK, all the way back to Internet Explorer.
  5. Navigate to your Sharepoint site.
  6. When signing in, click, "Remember me".
  7. (optional) After signing in, close Internet Explorer, and re-open it. Navigate back to your Sharepoint site and see if it remembered your credentials.
  8. Open your Access database. It should now connect properly.

Answered by fbueckert on December 19, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP