Can multiple users access one front end of a split database?

[sbrown106]

Hi everyone,

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

1. Start Access and under File, click Options.
2. In the Access Options box, click Client Settings.
3. 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?

Thanks for any guidance

 

[Minty]

The real answer is 1.
You might get away with more, but I suspect corruption and other issues will raise their ugly heads very quickly.

It is simply not recommended. Imagine all sharing the same Excel workbook, and how much grief that causes.

 

[The_Doc_Man]

I concur with Minty's answer.

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.

 

[sbrown106]

I concur with Minty's answer.

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.

thanks for that - I know its best practice for the database to be split - would that same advice apply to a single database that hadnt been split?

 

[isladogs]

Yes

 

[theDBguy]

Yes

+1

 

[Pat Hartman]

thanks for that - I know its best practice for the database to be split - would that same advice apply to a single database that hadnt been split?

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:

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.

 

[The_Doc_Man]

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.

 

[widemonk]

I realise this in an old post. Found it while trying to justify to a colleague about multi-users not using the same front end.

@The_Doc_Man I notice you mentioned about "giving out individual copies for use on local machines." and @Pat Hartman, you've include a nice batch file example. Those points got me wondering...

• Is it OK for multiple front-ends, one per user, to be run from a shared network folder as opposed to local machines?

Perhaps in users own individual network folders, or even a common folder where each copy would include peoples name/initials 'databaseJIM' 'databaseDAVE' and 'databaseSTEVE' etc?

I would prefer the network option because it allows me to create new features and redistribute updated front-ends automatically without emailing individual attachments or personally visiting everyone with a memorystick etc ? Day-to-day, users might not even notice an upgrade has been given.

If not, I may have to go with some variant of the batch file solution

Thinking out loud now...
> Include version control to indicate to a user on the db main menu that a new version is available.
> Can (vba) code similar to that be run from an 'update now' button that is only displayed when new version is available?
> Disable main menu and open temporary new menu if current version is > two updates old, to force an update.

Thanks

 

[theDBguy]

I would prefer the network option because it allows me to create new features and redistribute updated front-ends automatically without emailing individual attachments or personally visiting everyone with a memorystick etc ?

As already mentioned, this is not an insurmountable problem as you can use the sample batch file to automate the version updates. But yes, the important aspect is to have each user use their own copy of the FE. It can be local or on the network.

 

[The_Doc_Man]

The only down side to having users with private FE copies on a shared system is that Access is still on the local computer. EVERYTHING YOU OPEN has to cross the network to get to the place where it will be working. Even with a gigabyte-speed network, you will have a little extra latency as your network struggles to download not only your data tables but your queries, forms, reports, macros, and modules. And relationships. There will also be a small but non-zero delay component for remote file locking because the host of the file is always its lock manager. With local files, your lock data structures are in a memory area called the system scratchpad. With network files, those data structures are on the remote host's scratchpad, so have to be fetched for every file interaction. Hope you have a robust network.

 

[Pat Hartman]

• Is it OK for multiple front-ends, one per user, to be run from a shared network folder as opposed to local machines?

There is no advantage to doing this and as Doc has pointed out, there are downsides. Having the FE on the network increases the overhead of working with Access. Access has to be loaded into memory on your local computer and parts are swapped into and out of memory as needed. This is more efficient when the FE is local than when the FE is on the LAN. Regardless of whether the FE is local or LAN based, ALL the data still needs to be transported to memory on YOUR computer. With Access, NOTHING happens on the server.

I would prefer the network option because it allows me to create new features and redistribute updated front-ends automatically without emailing individual attachments or personally visiting everyone with a memorystick etc ? Day-to-day, users might not even notice an upgrade has been given.

Take a look at the batch file again. Notice that the FE is always stored in a NETWORK folder. You never email anything to anybody except perhaps mailing a shortcut to a new user the FIRST time he has to access the app. Once the users have the shortcut installed on their desktops, each time they run it, the batch file copies a NEW copy of the master FE to the local drive. I do this for a reason. My choice was not arbitrary. Some people use a database that checks versions and only updates the FE if it is a different version than the BE.

I prefer to download a fresh copy for two reasons.
1. It eliminates any problem with bloat since the new FE is always as small as it can be.
2. I always use querydefs for my queries because I can reuse them and because Access calculates an execution plan the first time a querydef runs and then saves it for future use. Calculating the execution plan is pretty fast so on a one off basis, you would never notice a lag between running a querydef and embedded SQL in your VBA code BUT, if you run queries inside of a loop or multiple times each day, t shaves a tiny bit of time off of the process.

I avoid like the plague pushing out FE or BE updates - especially BE updates, during normal work hours. I only do it if there is an emergency fix required even though the separate FE downloaded to each user's desktop enables me to push out updates during the day. If the change is localized, I usually just send out an email with the information on what was changed and tell the users, if they need the new version to close Access and reopen it. If I need everyone to shut down, I flip a switch that makes everyone log out of the application at a scheduled time and replace the FE during that window. Preferably at lunch time to minimize disruption. IT people spend late nights and weekends doing support work. On more than one occasion, when I was doing mainframe projects, my young daughter spent the night on a sofa in my office or even in the ladies room if my office didn't have anything comfortable to sleep on if my husband wasn't going to be home at the time my team was doing a major install and that always happens during off hours unless there is an emergency.

Making BE changes is always more complicated since the old FE will likely not work with the new BE so your update needs to be more formal. I always do this type of change off hours in the evening or on weekends. Once an application has been in service for a month or so, BE updates are rare and even FE updates are limited unless you started with just a basic version of the app and are adding additional features regularly.

The batch file is a very simple distribution method but I normally include version checks inside the database. There is a local version table in the FE and a separate table in the BE. When the app opens the first thing it does is to compare the two values. If they are different, the app displays a message to open the app using the shortcut and closes. In theory, this check wouldn't be necessary if you could trust users to follow directions. But, sometimes they leave Access open over night or even for a few days so they might have missed an update. Or, they happened to notice the .accdr file in the folder on their PC and open that instead of using the shortcut.

 

[The_Doc_Man]

Pat's #1 suggestion (auto-download each time eliminates bloat) is actually an important consideration if (A) the FE is very big and/or (B) a lot of UPDATES or DELETES will occur - because I believe the working area for the two types of queries I named are in the workspace of the FE file. The auto-download lets you pretty much ignore whatever leftovers remain from what you did in your last session.

The ONLY downside of the auto-download is if you for some reason have to have local FE tables that are statically populated. Well, ... unless your IT chief is a real dog about things downloading to a user folder. Then you would need to at least talk to said person to explain what you are doing and why you are doing it.

 

[GPGeorge]

thanks for that - I know its best practice for the database to be split - would that same advice apply to a single database that hadnt been split?

Even more emphatically for an unsplit accdb, yes! Do not allow it to be shared, assuming of course, there is value in the data for the organization.

 

[widemonk]

Thank you for prompt responses. All makes sense.

My own FE is actually local anyway. Its the FE for the others that is server-based.
Explains why I've not experienced any latency but still good to know.

Thanks @Pat, that's almost the same thing I was going to do with version check, although the FE version# held in a simple unbound and hidden textbox on the initial menu when loaded.

Having it server-based was also a factor in eliminating bloat, but agreed, no reason why the update batch file wouldn't solve that too.


Just a little perspective...
We have an IT chief ?? Haha no, we're a small enough organisation and don't have that guy.
The FE is currently < 2mb and BE < 3mb and holds data for last 9mo.

Initially created for my own purposes but it includes certain info that colleagues have found useful. I found it easier to make it a shared resource so they can all access, rather than have people keep making certain queries about the data or for me to print reports on their behalf. With that in mind, FE updates may occur semi-regularly as colleagues send update / additional feature requests.