Forum Discussion

dajohnson162000hot's avatar
dajohnson162000hot
Copper Contributor
May 28, 2020

MS Access

All of my tables are shared/linked to Sharepoint. If I add something in a table will it automatically update Sharepoint? If yes, then where do I find the lists/tables in Sharepoint? Is the link from ...
access
dajohnson162000hot's avatar
dajohnson162000hot
Copper Contributor
Thanks George--I suspected I was answering my own question, now to the heart of the problem...I have multiple users from multiple workstations trying to access an MS Access database I have created with all of the tables linked to SharePoint, (The Access database has been download to each workstation). Will multiple users through forms, be able to add data simultaneously to all tables?
George Hepworth's avatar
George Hepworth
Steel Contributor
May 29, 2020

dajohnson162000hot Yes, it works the same way as a standard Access accdb FE and BE design. It's just that the BE is actually the SharePoint lists, on linked tables in a separate accdb. Plus, you get a bonus with SP lists. The synching mechanism for SP lists is quite good, meaning one or more users can disconnect from the internet and continue working locally with the Access accdb with a high level of confidence they can reconnect and re-synch to the SP list changes that occurred in the interim. There's a significant amount of manual intervention involved, but it's reliable.

  • dajohnson162000hot's avatar
    dajohnson162000hot
    Copper Contributor
    Jun 03, 2020

    George Hepworth Thanks George for your response. How do I find the "Sync" button in Access? Also, I am assuming that if I make any changes to the Front End I will need to have all the users download the revised ACC file to their machines again. Is that correct? 

     

    • George Hepworth's avatar
      George Hepworth
      Steel Contributor
      Jun 04, 2020

      dajohnson162000hot 

       

      When you use SharePoint lists as your tables for an Access relational database application, you are able to cache a local copy. Specify that option here and do not check the option to clear the cache.

       

       

      What will happen is that, when a user disconnects and works offline, their work is stored in that local cache. Then, when they reconnect later, Access compares their local cache to the live SharePoint lists. If there are differences, i.e. additions, updates or deletions, Access will present the user a dialog allowing them to accept or reject each difference. This works quite well, but if the number of changes is large, and if multiple users have all changed the same record, it can be a bit tedious to work through and make good decisions. 

       

      So the synching is triggered automatically when the accdb detects a live connection and does its check between what is locally cached and what is now stored in the SharePoint lists.

      • dajohnson162000hot's avatar
        dajohnson162000hot
        Copper Contributor
        Jun 04, 2020
        Thanks George. I understand better the syncing relationship between SP lists and Access tables. What if I add new forms etc. to my front end local copy and then upload it to SP, will other users see those new forms without downloading the new front end locally to their machines?

Resources