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

 

[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.

 

[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.