May 29 2020 07:15 AM
In a way, the question answers itself.
These "tables" are physical lists on your SharePoint site. Access links to those lists/tables. When you work with those Access tables, you are, in fact, working indirectly with the lists/tables. Adding, updating or deleting records remotely via the Access interface is that physical act as doing it directly in the SharePoint environment.
I've always found the ever-modulating SharePoint interface a bit daunting to navigate, but persistence will lead to you those lists which are represented as tables in your Access accdb.
The linking mechanism is different, technically, from ODBC links, but functionally very similar. If it's important to be able to work in that aspect, it'll be worth investing time in searching for resources on Microsoft's sites that explain how the linking is done from a technical POV. For most of us, that's a bit of overkill in the majority of Access relational database applications.
May 29 2020 10:56 AM
May 29 2020 11:28 AM
@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.
Jun 03 2020 11:18 AM
@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?
Jun 04 2020 05:53 AM
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.
Jun 04 2020 10:01 AM
Jun 04 2020 10:18 AM - edited Jun 04 2020 10:23 AM
@dajohnson162000hot Sorry, I forgot to address that portion of your query.
Each time you create a new version of the Front End accdb (or the compiled version accde) you will have to distribute that new FE to each user.
One way to think about this is to put it into the context of all relational database applications.
They have a data layer, which consists of the tables holding the data. In Access, that can be local Access tables, but it can also be any one of a large number of data storage tools, including the SharePoint lists. In other relational database application, the same is also true. A web page, for example, can work with data in SQL Azure or SQL Server, MySql, and so on.
They have an interface layer, which consists of forms in Access, or web pages in a browser-based application.
They have a logic layer, which consists of the code that manipulates both data and objects.
In the Access version, the FE accdb (or accde) is the interface layer. Access can connect to a data layer which is another accdb, a SQL Server database, or, in this case, SharePoint lists, and so on.
What you are doing, is in essence, putting the interface, or FE, on a SharePoint site for deployment to each user. The SharePoint lists are separate, i.e. they are part of the data layer.
Jun 08 2020 04:09 PM