1

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?

  • This probably won't help, and my brain is fading.. A long long time ago in SP2007 days we had to reconnect on every workstation because Access uses something at Operating System level to establish the connection to SharePoint. I would start with just one table/connection and see if that works. It sounds logical that you can not transfer authentication in Access files.. what would happen if such a file got in the wrong hands? – Danny '365CSI' Engelman Dec 28 '16 at 14:00
  • Danny - many thanks, I'll give it a go and post back with the outcome – GeoffE Dec 28 '16 at 15:48
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.