Jul 07 2020 12:41 PM
Jul 07 2020 12:41 PM
I've built a database now I need to share it with the group. How do I do it?
Jul 07 2020 01:57 PM
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.
Jul 07 2020 03:28 PM - edited Jul 07 2020 03:28 PM
Jul 08 2020 11:23 AM
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?
Jul 08 2020 02:53 PM
@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.
Jul 09 2020 03:09 AM
Jul 10 2020 10:34 AM
@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.
Jul 27 2020 05:43 PM
I had tried replying on a different thread but was unable to actually submit via that form. I hope this post is appropriate here.
I found the comments in this post super helpful.
I have, what I consider to be, a small Relational DB
8 Static Tbls. tbl1 related to tbl2, tbl2 related to tbl3 ... tbl5 related to (Joining tbl6) tbl7 related to tbl6. & tbl4 related to tbl8. The design for those 8 tables shouldn't "air quotes" change ever. the problem that I would have is having to create a button that would generate a form that would allow Mgmt to create tables with columns they or I would create on the fly. Given the fact all of members in out team live across the US, an RDP solution would have to be the way to go. How instantaneous, or not, would the change to the DB be made if a team member claimed a record to edit it?
My question makes sense to me, but it may not to anyone else.
Any 1 of 15+ people will be editing a set of data. No 2 people should edit the same record.
If a column named claimed = Yes once a person clicks the Claimed button, how soon after the click will the change occur in the DB given the RDP solution.
I hope this all makes sense and that I can pull off the Access solution b/c I'm not much of a developer. Thank you for any advice you can provide.
Jul 29 2020 05:20 AM - edited Jul 29 2020 09:50 AM
I would NEVER give users the ability to create tables on the fly, EVER! This is a recipe for disaster.
Database design needs to be managed, properly thought out our else you'll end up with a total mess and people will blame Access our you for all the problems.
Think long and hard before continuing down this path any further.
As for how instantaneous changes are... in a split database the changes are reflected almost instantaneously. Once the user save there changes, moves to another record, close the form, ... the change is committed and available to other users. That is the beauty of a multi-user database.
Jul 30 2020 08:01 PM
Thank you kindly for the reply. I appreciate you.
I had considered the "Allowing Management the ability to create tables on the fly via a form" and wasn't too sold on the idea. Going to have to table that for now as it's not something I agreed to do.
If I may, one further question. Can you pleas shoot me a link to the best instructions you have seen for connecting SQL Server Express to Access. I'm trying to develop with scalability in mind from the get go.