Forum Discussion
How do I share my Office 365 access database with my MS Office Family?
I've built a database now I need to share it with the group. How do I do it?
There are a number of things to consider, and we don't really have quite enough information to touch on them all, so let's start with the basics.
First, your Access relational database application MUST be split into two files. One of these accdb files will contain only the tables where the data is stored. We refer to this as the "Back End" or "BE". The other accdb file will contain only the forms, reports, queries, and macros or VBA. We refer to this as the "Front End" or "FE". There is a built-in function to do this.
Once you have done this, the tables will be linked into the "Front End" and they will show in the Navigation Pane with a small arrow in front of them to indicate that external link.
Now, place the BE in a location where ALL of the people who need to use it have permissions. This could be, for example, on a network. Since this is your own group, I assume there is such a shared LAN location.
DO NOT attempt to use a OneDrive or SharePoint site to do this. Access is not designed to work in those locations.
Now, give each person who will use it their own copy of the FE. Never let two users share the same accdb FE. Only let them use their own copy, which you place on their own computers.
From there, there may be additional considerations, including perhaps some modifications if performance isn't acceptable on a shared relational database application, but you probably won't.
Note that YOU as the developer, must keep the original copies of both the BE and the FE in a location only you have access to, such as your computer. DO NOT USE that "master" copy for production. It's only there for future development and enhancements, and in case something goes wrong with one of your users' copies.
Each user must have their own copy of Microsoft Access to be able to use it. If they don't, you can have them install the Runtime which only runs Access relational database applications, but can't be used for modifications.
Make sure your IT support implements a rigorous backup protocol for the data in the accdb BE.
Good luck.
Thank you George. Some years ago I was SQL Server/BI developer so I know something about splitting the database. I do appreciate the need to split it which I will do and keep a master copy. Unfortunately, we do not have a LAN. The users will be located in different town around our region. They are the board members of a local activities club. The club purchased a subscription to MS Office Family so the six board members could share information and the database. I was hoping there would be a way to do this through the “family” subscription. Any suggestions?
- Running an Access backend is dependant on a LAN. That said, you do have some options available to you, refer to:
https://www.devhut.net/2016/09/24/access-back-end-location-wan-online-server-onedrive-dropbox/ roguefem Since you are already familiar with SQL Server, that's the way I would go. Use either Azure or a hosted SQL Server database. Move your tables into that. Link them into the Access FE. Give each user a copy of the Access FE.
I don't think I know enough about MS Office Family to be able to comment in depth, but my understanding would be that it's simply a way to obtain copies of Office for several people and to provide a common email system. So it doesn't apply.
That said, another option MIGHT be to consider moving the Access tables into SharePoint as SharePoint lists in lieu of a SQL Server backend. There are some limitations there, but it can work for some relatively smaller relational database applications.
George Hepworth Thank you for the ideas. This is a TINY database. I think I'm going to simply put a copy on the operator's computer of both the FE and BE. I will have a master copy which won't be up to date, but at least will work if something happens to her copy. She can upload lists and reports to the "family" as needed instead of emailing them every month.
I don't think the club will pay for anything more so I need to find something free. I may experiment with SharePoint lists.
