Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-24-2019, 03:31 PM   #1
jmark@stayintouch.us
Newly Registered User
 
Join Date: Apr 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
jmark@stayintouch.us is on a distinguished road
Front End Deployment of Split Database in a small enviornment

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!

jmark@stayintouch.us is offline   Reply With Quote
Old 04-24-2019, 03:48 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,671
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Front End Deployment of Split Database in a small enviornment

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.
MajP is online now   Reply With Quote
Old 04-24-2019, 04:57 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,563
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Front End Deployment of Split Database in a small enviornment

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
MajP (04-24-2019)
Old 04-24-2019, 07:15 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,073
Thanks: 81
Thanked 1,597 Times in 1,481 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Front End Deployment of Split Database in a small enviornment

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 04-24-2019 at 07:23 PM.
The_Doc_Man is offline   Reply With Quote
Old 04-24-2019, 07:21 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,073
Thanks: 81
Thanked 1,597 Times in 1,481 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Front End Deployment of Split Database in a small enviornment

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 04-24-2019, 07:22 PM   #6
shadow9449
Newly Registered User
 
Join Date: Mar 2004
Location: Toronto, Ontario
Posts: 962
Thanks: 7
Thanked 40 Times in 38 Posts
shadow9449 will become famous soon enough shadow9449 will become famous soon enough
Re: Front End Deployment of Split Database in a small enviornment

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.
shadow9449 is offline   Reply With Quote
Old 04-24-2019, 09:13 PM   #7
jmark@stayintouch.us
Newly Registered User
 
Join Date: Apr 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
jmark@stayintouch.us is on a distinguished road
Smile Re: Front End Deployment of Split Database in a small enviornment

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!

jmark@stayintouch.us is offline   Reply With Quote
Old 04-24-2019, 09:21 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,563
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Front End Deployment of Split Database in a small enviornment

Quote:
Originally Posted by jmark@stayintouch.us View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 04-24-2019, 10:24 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,845
Thanks: 411
Thanked 688 Times in 667 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Front End Deployment of Split Database in a small enviornment

You could start here
https://btabdevelopment.com/free-access-tools/

Personally I prefer to copy FE only when needed.


Quote:
Originally Posted by jmark@stayintouch.us View Post
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!
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 04-25-2019, 01:45 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Front End Deployment of Split Database in a small enviornment

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 04-25-2019, 01:55 AM   #11
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,845
Thanks: 411
Thanked 688 Times in 667 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Front End Deployment of Split Database in a small enviornment

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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Reply

Tags
split db front end

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] replace line of vba code during split form front end deployment Hutchy Modules & VBA 6 08-11-2015 06:33 PM
Split database: Can you have different front ends? bo8482 Theory and practice of database design 10 03-31-2009 03:43 AM
Runtime Deployment for a Split Database Sandpiper General 1 01-18-2007 01:48 PM
Split a database- where's the front end? p595659 General 3 07-11-2006 05:41 AM
Compiling front end of a split database jeo General 10 05-21-2003 11:40 AM




All times are GMT -8. The time now is 12:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World