I have split up my database the backend is in a shared folder and the front end has been copied onto each users machine (half a dozen users).
It may be necessary for the moment to allow all users to access one shared front end, there are not many users, 6 at the most and not always at the same time - is it possible to have one front end and the users access that one front end on a shared drive ? I'm using ms access with office 365. Ive had a look on the web and the suggestions made below, but having difficulty finding those options
Start Access and under File, click Options.
In the Access Options box, click Client Settings.
In the Advanced section, under Default open mode, select Shared, click OK, and then exit Access.
How many users at the most would you suggest be able to access the database in this way safely?
The problem is file locking. If there is a form that everyone is likely to use, the first person will lock it and there is potential for every other intended user to get file lock issues that might corrupt information. Further, let's say that two people DO get in, perhaps using different forms, and BOTH want to add a record from the respective forms. They will BOTH probably find the same free space for their new record and whoever got there 2nd will lose (badly) when the form finds that the location is suddenly occupied.
The main problem is called "destructive interference" which, in this case, means "the left hand doesn't know what the right hand is doing" - and thus gets things all bollixed up.
An FE should never be shared. It should be copied and shared by giving out individual copies for use on local machines.
"Necessary for the moment" means "don't use it for the moment." There is never a time when it is totally safe to share front-ends. Never.
The problem is file locking. If there is a form that everyone is likely to use, the first person will lock it and there is potential for every other intended user to get file lock issues that might corrupt information. Further, let's say that two people DO get in, perhaps using different forms, and BOTH want to add a record from the respective forms. They will BOTH probably find the same free space for their new record and whoever got there 2nd will lose (badly) when the form finds that the location is suddenly occupied.
The main problem is called "destructive interference" which, in this case, means "the left hand doesn't know what the right hand is doing" - and thus gets things all bollixed up.
An FE should never be shared. It should be copied and shared by giving out individual copies for use on local machines.
"Necessary for the moment" means "don't use it for the moment." There is never a time when it is totally safe to share front-ends. Never.
A THOUSAND times yes!! When you have a monolithic database shared by multiple users you dramatically increase your risk of corruption. When an Access database is opened, objects and data are moved to memory as they are referenced so think about it, you have 6 concurrent users all of whom have parts of the same file in memory at the same time. What could go wrong?
Splitting the database is done for three reasons.
1. minimize opportunities for corruption
2. speed
3. ease of replacing the FE with an updated version.
Windows manages file sharing. Access, on top of that, has additional sharing management tasks. Ever notice that anytime you open an Access database, a new file with the same name but a different extension magically opens up in the same directory? That is Access giving itself a place to keep track of who is logged in and what they are doing to what objects. In a split application, whenever anyone access the BE either directly or via something that touches data from the FE, Access also opens a lock file for the BE. In a busy environment, the BE lock file stays open all the time OR for efficiency most people open a hidden form linked to a dummy table and that keeps the BE open throughout the process. This eliminates all the create/delete activity for the Lock file for the BE. It is always open and Access just logs events.
PS - Access is multi-user straight out of the box. You do not need to mess with the settings, nor should you.
I'm not sure what your comment about sharing the FE "for now" is all about. Do you not know how to automate distributing the FE to users so that you can easily replace the FE with a new version as needed?
The simplest method is a four-line batch file. You will see other much more complicated batch files and you will see several example databases that manage the distribution. Copy any of them but make sure you actually understand how they work. Here's the batch file I use:
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
It is stored on the server, usually in the same directory as the master copy of the FE and runs from a shortcut on the user's desktop. It makes a directory first so that a new user doesn't have to do it himself and the name is always valid. If the directory already exists, the command is ignored. It deletes any existing copy of the FE.. Again, if the file does not already exist, there is no error raised. The third step copies the master of the FE to the local PC and the fourth step opens the app.
Absolutely you should not share a monolithic database other than by letting people work with it one person at a time, perhaps based on a schedule. If your people have to work with the app simultaneously, you cannot safely share the FE other than by having them copy it. In case you were wondering, the correct way to share the BE is that the FE needs to never use what is called "Pessimistic Locking" on any query or form. Use either "Optimistic Locks" or "No Locks" for BE updates.
