Front End Deployment of Split Database in a small enviornment (1 Viewer)

Local time
Today, 00:07
Joined
Apr 25, 2019
Messages
62
I have 15 users that use their own copies of the Front End of a single, small (30M) database. Every time I make a change to the FE, which is frequent, I have to push out the new FE to the desktops of all 15 users. All users use the same kind of PCs, the same Windows 10, and the same Access 2019. The BE is hosted on a shared network folder that all users have full access to. The question is: is there any significant reason that I cant have just ONE FE located on the Shared Network and have shortcuts to this single FE located on each users' desktop so that they all basically are opening the same FE over the network? This would make my updating infinitely easier. Can anyone shed any experienced light on this? thanks in advance!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:07
Joined
May 21, 2018
Messages
8,463
Do not do it. Think about it. If you put a single front end on the network every user is pulling those forms and reports over the network. Expect performance to go way down. You cannot establish user preferences. You are likely to corrupt the FE.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:07
Joined
Oct 29, 2018
Messages
21,358
Hi. I agree, this would be a very bad idea. Shared front ends are prone to corruption. Also, depending on your database functionalities, if you're using any temporary objects like tables or have some sort of user preferences features, those won't work very well with a shared front end. You might want to do a search on "front end updater" because there are plenty of utilities and samples on how to avoid deploying the FE updates manually to every user's desktop/machine. You can have code do it for you automatically.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:07
Joined
Feb 28, 2001
Messages
27,001
Here is why you don't do this: File locks. Hold onto your hat for this one.

Sharing a FE means you have to take out a Windows file lock on every file you open (and leave that way). So EVERY TOUCH of the shared file now triggers File Access Arbitration. OK, if you opened it shared, the arbitration will be allowed to go through. But the arbitration involves a quick network exchange that you probably wouldn't notice.
Not so bad for a quick exchange, you say? Multiply it times 15 if every one of your users is online at the time.

But I'm not done. With Windows, arbitration is handled by a bit of software called the Distributed Lock Manager. Why distributed? Because on a network, the file system that "owns" a file is the file system that manages the locks for the file. Let's take the good part of your question first: You have a split FE/BE case.

You absolutely CANNOT do anything about the BE because for it to be useful, you have to share it. But if you pick query and form opening modes that minimize locking, you can minimize the window of exposure to the time that the query/form is actively doing something. Optimistic Locks or No Locks will lead to that minimization. The data you transfer from the BE to your local system is necessary because work can only be done in your local system's memory (which is where the Workspace is). Therefore you have to accept that sharing and the network load it represents as a necessary overhead. Some IT network wonks would say "necessary evil."

Before I leave the BE for the moment, you have to understand that Access manages its own APPLICATION-LEVEL locks in the lock file that resides in the same folder as the BE file. These are the .LDB files that record your actions in the database that you opened. So every time you touched the .MDB or .ACCDB file, went through arbitration, or other security-related things, you ALSO did arbitration and mucking about in the .LDB file. Again, for the BE's lock file, you've got little or no choice.

But what about the FE? There, you have the forms and code segments that you must open and bring down to the memory on your local system. I'm not talking about the form's .Recordsource, but the form itself, which is a data structure from the AllForms collection inside the FE file. IT gets locked via the FE's .LDB file which is in the same folder as the FE itself.

When you have multiple users hitting the form, you are making Access work with the lock file from the FE file as well as the locks for the BE file, over and above what Windows requires. So now you have a network exchange to diddle around in the lock file BEFORE you get to diddle around in the FE file itself.

As if that wasn't bad enough, Windows File Arbitration for that lock file WILL involved as well. So you have all that stuff going on behind the scenes over your network. If you don't have at least 100 MB Ethernet, your network will slow to a crawl.

BUT if everyone has their own copy of the FE, let's look at locking a second time.

The BE issue is what it is. No change. Nothing can change that as long as you stay with a shared Access BE file.

However, the FE is now LOCAL to your workstation, which means that your LOCAL file system arbitrates access to that local file. And since everyone has their own copy, it doesn't MATTER whether you opened the FE exclusive or shared - nobody else will be working on it because everyone else has their own local copy, too. No network involved. Arbitration is at MEMORY speed (a few hundred nanoseconds), not NETWORK speed (several milliseconds), for all FE interactions. Even for the FE lock file, THAT is local and operates at DISK speeds (tens of microseconds), not NETWORK speeds.

Since everything in a local copy of the FE is private, you cannot see a collision with another user. Can't happen unless your company is riddled with hackers. You can't trip over someone else who is opening the same form that you are - because it is a different COPY of the same form. With Optimistic or No Locks, even the commonality of that form's .Recordsource pointing to the same BE table or query won't make a difference; just don't use Pessimistic Locking. For Optimistic or No Locks, the touch to the BE is VERY fast and quickly finished. So you have good odds of avoiding usage conflicts. (Which, by the way, is the primary source of database FE corruption.)

TheDBGuy is correct to point out that if you attempted to use FE-based tables as temporary locations, the shared FE is going down like the Titanic because of usage conflicts there, too. We don't always know the mechanism, but remember that you are drawing from the same exact disk blocks for those putative shared FE files AND unlike a quick table touch, the Form or Report WILL be open for seconds or minutes. A VERY wide window of opportunity for usage collisions.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:07
Joined
Feb 28, 2001
Messages
27,001
I'm posting a second time to assure that this suggestion gets seen separately. I am seconding the suggestion of TheDBGuy regarding a "Front End Updater" method that has appeared in this forum many times from multiple sources. Your problem of how to assure that everyone is up to date is TRIVIAL if you used any of the various types of updater posted in this forum using the suggested search topic to find same.
 

shadow9449

Registered User.
Local time
Today, 01:07
Joined
Mar 5, 2004
Messages
1,037
To simplify the answer:

Having multiple users in an FE over the network isn't really much better than not splitting the application in the first place.

What I do is write a small batch file that updates the local copy of the front end when the user logs into Windows, or if I suspect that they aren't logging out or turning off the computer overnight, I have their desktop shortcut copy a fresh copy of the FE from the server to their workstation and then open it. It takes less than an extra second and then I'm free to make FE updates as much as I want and everyone gets their own copy the next day.
 
Local time
Today, 00:07
Joined
Apr 25, 2019
Messages
62
wow! SO much thanks to all of you and especially TheDocMan, for taking the time to write so much detail! You have very effectively scared me into NEVER doing what I was hoping I would be able to! No worries. I will now search out the updater options or write my own .bat file to deliver current copies of the most recent FEs. Thanks again to everyone and case closed! Enjoy!:)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:07
Joined
Oct 29, 2018
Messages
21,358
wow! SO much thanks to all of you and especially TheDocMan, for taking the time to write so much detail! You have very effectively scared me into NEVER doing what I was hoping I would be able to! No worries. I will now search out the updater options or write my own .bat file to deliver current copies of the most recent FEs. Thanks again to everyone and case closed! Enjoy!:)

Hi. Good luck and let us know how it goes.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:07
Joined
Sep 21, 2011
Messages
14,048
You could start here
https://btabdevelopment.com/free-access-tools/

Personally I prefer to copy FE only when needed.


wow! SO much thanks to all of you and especially TheDocMan, for taking the time to write so much detail! You have very effectively scared me into NEVER doing what I was hoping I would be able to! No worries. I will now search out the updater options or write my own .bat file to deliver current copies of the most recent FEs. Thanks again to everyone and case closed! Enjoy!:)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:07
Joined
Sep 12, 2006
Messages
15,614
The only difficulty I have found is replacing/overwriting a front end database while it is in use.

What I have is a required version in the backend, and I update that with each software release. The versionnumber in the front end gets checked against the required version in the backend, and if it is lower than the required version it refuses to run. Now what I ACTUALLY do is copy the new version from a server to the same folder as the user's front end (currentproject.path), and ask him to use that in future, and to manually change any desktop short cut. What I am reluctant to do is overwrite a running database.

How do you all get round that one?

I also have startup code that prevents the front end running in any mapped drive above letter "G" say. Those pesky users always manage to try to run the master copy stored on the server, instead of copying it to their local folder.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:07
Joined
Sep 21, 2011
Messages
14,048
The update method I copied and used, checks on startup and if a new version is required, closes the DB and runs a batch file it has just created to copy latest FE, deletes the batch file and then starts the DB.
 

Users who are viewing this thread

Top Bottom