Multi-user with network-shared FrontEnd? (1 Viewer)

[jhg]

I have to update a split Access DB from single-user to multiple
users. All persistent data is in the BE, FE contains the app plus a
few tables that would be TEMPORARY if Access supported the concept.
The client has access only to the runtime, not the development
environment.

I see three options:

1) Each client gets a copy of the FE

2) Single FE shared over the network

3) Single FE, containing NO tables, shared over the network. Any temp
tables needed are linked from a "local" BE for each client.

I have 30+ years as an enterprise DB architect/software engineer
(Postgres, Oracle, MySQL, Java) but limited Access experience. This
is a volunteer project for a local club to which I belong.

My assessment of these 3 options would be:

1) OK, but a real pain when it comes time to push a FE update to the
clients, especially when some of those clients may not be online
all the time (laptops).

2) This sounds tricky, since all the FE temp tables would have to be
multi-tenant (client host column) to avoid conflicts, and there
would likely still be lock contention.

3) This sounds like the best option. Each client's local temp BE
would isolate the FE's transient data to that client. The DB
startup code could even recreate the local temp BE each time.

However, everything I read says a network-shared FE is a recipe for
disaster and DB corruption.

Can someone explain exactly WHY this is?

Or, does the "disaster" designation only apply to option 2 above? Is
it safe if the FE contains no tables?

 

[jdraw]

Here is an often referenced site FMS that hits on the area you are asking about.

Each user gets a copy of the frontend on their PC/laptop.
Tables all go in backend.

 

[jhg]

I understand that's one canonical solution, but it isn't optimal when pushing out FE updates.

I want to understand the actual source of the problem to determine if the no-tables solution will work.

 

[jdraw]

Well, you will get other comments.
You may get some insight from this post.

There are many posts in various forums that deal with automating the updates to front ends of Access split databases.
Try a google search. "ms access automate front end updates"

The Doc man on this forum often responds to technical questions and details. He is on most of the time, and once he reads your post, I'm sure he will advise and explain things.

 

[theDBguy]

1) OK, but a real pain when it comes time to push a FE update to the
clients, especially when some of those clients may not be online
all the time (laptops).

Hi. Welcome to AWF!

In my humble opinion, this (option #1) is the optimal solution. If users are not connected to the network (online?), how could they even access the one shared FE on the network? Updating the individual copies of the FE is not really a problem. That process can be automated (similar to how other software would notify the user to download an updated version of the program. It can also be made as an automatic update without any prompts).

 

[The_Doc_Man]

Thanks for the vote of confidence, jdraw.

@jhg, you asked for an explanation. Of the three cases you mentioned, only one of them is viable with unknown sharing. ANY TIME you have a single file being shared, you run into locking issues and "invisible interference" issues. In the post in jdraw's 2nd line, I have posted details of why you NEVER want to share a front-end file. Run through the posts in the entire thread as referenced.

I will more directly mention a couple of your concerns.

First, you are right and wrong that only the "separate FE copies" case can truly have temporary files. There IS such a thing as having a template file that you could use as a third place to have things. Pat Hartman has described this many times. You just delete any local copy of this template file and then use the FILECOPY operation to bring down a virgin copy of the template. Open it, dynamically map the tables therein, and you are off and running. However, that doesn't solve the issues of sharing the FE file directly to each user. I'll get to that in a moment.

You said you worry about downloading a new copy of the app to each user if they are not online, but that is a false concern. The REAL concern is if you published a new copy while someone else was active. We often use auto-updating self-launching scripts and Pat Hartman provided an example in that same thread jdraw referenced earlier. If nobody is online at the time you post the update, then the auto-updater script is what your users REALLY launch, after which they always run a fresh copy of the most recent posted FE file. They might see the disk light flicker a couple of times, then the app opens up, fresh and clean.

If you had temporary tables in this FE file, they have to be TRUE temporaries - i.e. start each session empty and you don't care what happens to them when you are done. If any data has to be persistent, it has to be in the BE file and in that case, if needed, it must marked by whose data it is. If this temporary table can start life new every time you start the app, it can be temporary in the FE file. AND doing it that way means that you discard the prior front-end after which you download a NEW FE that has no database bloat (if you posted a clean copy thereof).

You can NEVER EVER directly share a front-end. EVER. To understand why, consider this: Where is the work being done? Answer: On the machine that is actively hosting the FE file - which is each individual workstation. The file system of Windows, to run a file from a remote directory, has to down load a temporary copy of the file to the local machine because that is where your user is logged in and has all his/her permissions. The CPU of your local workstation has to have the program immediately available.

The active workspace of Access is ALWAYS local to the keyboard controlling it (with the sole exception of running RDP - Remote Desktop Protocol, but that is a different question for a different time). Here is why that is bad: Windows security FORBIDS a user on machine A from interfering with the actions of a different user on machine B, so the file system imposes file locks - which are managed by the machine hosting the file being shared. These locks, running across the network, are usually at least one order of magnitude SLOWER than the in-memory locks managed for local file copies. If you open something for modification and someone else opens the same thing, you have what is called a lock collision. This is bad.

Why do you get away with doing this AT ALL? Because Windows File & Printer Sharing protocol, also known as Server Message Block or SMB is a way to share a part of the file. But now you have two parts of the same file (or maybe two copies of the same part of the same file) being used by two machines for which Windows DISALLOWS communication under normal circumstances. Literally, the left hand does not know (and CANNOT know) what the right hand is doing.

The BE file is managed easily enough by the FE because Access's JET or ACE database engine is built to do quick in-and-out-and-done updates that last for milliseconds. But the FE has many juicy controlling structures - like forms - that will be open for a long time. As long as a form is open by multiple users, and that is often minutes - or hours, file lock interaction is likely to occur. If you don't have some very serious error checking, that FE lock collision will kill the 2nd process to get to a structure (because the 1st process on another machine has it locked.)

The error will almost certainly abort the process in mid-update. This is one of the main mechanisms by which BE table corruption occurs. Because Access rewrites tables after an update, and because of the need to support rollbacks, there are two copies of updated records for a brief time. During this time, anything that crashes a process in mid-update leaves the table half-written and totally unreliable. THIS is why you don't want to use physically shared FE files. You ALWAYS want to make copies.

 

[MattBaldry]

I have a batch file that I use to import the latest version of each "app" I create. I store the new FE in a folder with all images and such, and the user has a shortcut on their desktop to the batch file. When they click, it automatically clears the local C: folder and then copies over the shared folder from the server. It is not elegant, but it means I only need to upload the new FE to one folder each time.

With 24 clients, this is a much easier way. I am happy to share, but it was an idea I found from on here.

~Matt

 

[Pat Hartman]

Option #1 is easily supported by several methods.
1. a batch file that the user opens copies the FE from the server to the local PC and then opens it
2. a local Access database copies the FE from the server to the local PC, opens it and closes itself
3. More used in Citrix or a Remote Desktop environment - the login procedure copies the FE from the server to the user's personal directory and than opens it.

Additionally, if you need to use temp tables, the best solution is what you alluded to in #3. A separate BE to hold only temp tables. This is done with a template. When you start the process that loads the temp data, copy the template to the local directory to overlay the previous version. Since all the table names remain intact, no linking needs to happen. you're up and running. Keep in mind that you don't want to create queries that join these tables to SQL Server if your BE is SQL Server. In that case, put the temp tables in the server and tag them with the user ID. That allows you to delete all rows for a user and append new rows. SQL Server is far better about managing this delete/append without bloat than Jet and ACE are.

 

[gemma-the-husky]

@jhg

Your option 1 isn't a pain. That's pretty normal with any system.
Your backend stores a "required version" indicator.

When you release a new version, you change the backend to show the required software version has changed from say v2.78 to v2.79.
The front end v2.78 now refuses to start, and asks the user to update to the new version. Just explain to your users where the new version resides.

You can have temporary tables in the front end. That's not an issue. Some here recommend a side-end, but I have never bothered.

 

[isladogs]

I agree - its very simple to do your option 1.
In fact, by using a version number check, the FE update should happen automatically with no need for user action.

Although a batch file is probably the commonest solution, I prefer to use a separate starter app to do this (method 2) in @Pat Hartman's post.
I also use an API to do the copy to the local hard drive which makes the process very fast & optionally can provide a progress bar

 

[jhg]

Thanks for all the excellent info. The automatic front-end update might be doable. There will be issues when a combined BE/FE upgrade is pushed, and some clients still have a running FE instance that's no longer compatible with the new BE table structure... I guess checking the "required version" has to happen relatively often so down-level FE clients can get notified to update (and prevented from interacting with the current now-incompatible BE).

 

[theDBguy]

Thanks for all the excellent info. The automatic front-end update might be doable. There will be issues when a combined BE/FE upgrade is pushed, and some clients still have a running FE instance that's no longer compatible with the new BE table structure... I guess checking the "required version" has to happen relatively often so down-level FE clients can get notified to update (and prevented from interacting with the current now-incompatible BE).

BE updates can be done incrementally, not like FE updates that are often done by replacing the entire file. BEs can be updated to the new structure without losing existing data.

Sent from phone...

 

[Pat Hartman]

If YOU have control over the application, then you control when the update is applied. FE updates that don't have BE updates, can be pushed out mid day. You send the users an email and tell them to close and reopen. If this is an emergency fix, you have to find a way to force everyone to shut down, there are tools you can incorporate if you feel you might need to do this. BE updates are totally different. For these, there is no option. ALL users must exit the application. Again, a tool that forces a shut down can be helpful but there are other ways. BE updates should be scheduled for evenings or weekends so you have better control of the process.

If this is an application installed on a client computer and YOU do NOT control the updates, then you have to establish rules. I have an application that is sold to the public so I don't control the update unless the client wants to pay me to do it. I give them directions on how to ensure all users are out of the BE. I create a ver-ver update database which they must run if there are BE changes. The Update db validates the old version. It then checks to see if there are any users logged in. If not, it renames the BE so that no one can accidentally log in. It then backs up the BE by zipping it and placing the zip file with a date extension in the backup folder. Then it applies the updates and compacts the BE. It then makes a zip with a date extension in the backup folder. The FE is relinked to the new version. Someone has to open it and do some specific tests to make sure the update was successful. If it all works, the update db copies the newly linked FE to the master folder and zips it with a date extension in the backup folder. The last step is an email to the users telling them the db has been updated and they can use it again.

The app itself when it opens, always validates the FE/BE versions to ensure they match. If they don't, the app displays an error message with instructions and shuts down.

I use the batch file to distribute my FE. I use this method because as long as the FE validates the BE version, you should be OK and the whole process is much simpler than using a database to distribute new FE's. I also like the fact that the FE is replaced each time the db is opened. This may seem like overkill but FE's tend to be quite small and doing this ensures there's never a bloat problem and so far I've never had a corruption problem with any FE which can happen if a FE gets shut down hard do to some system failure.

 

[HackSlash]

I apologize, it won't let me post links but these are open source things you can find on the internet.

I use the Application Starter by peterssoftware to keep the FE always up to date.

The users always run the application starter, which is shared on the network. It performs backups of the BE automatically, and checks for updates to the FE. This can save your ass.

Then I use the back-end-update-utility from rogersaccesslibrary for BE updates.

I have two version numbers. One tracked in the FE and one tracked in the BE. The way the BE update utility works is that it runs a series of commands to update the schema of the live BE DB when the first person opens the updated FE. You need to do your own testing in a staging environment so you know it's going to work.

Goes like this:

1. Make your patches to the FE and BE
2. Download a copy of the live BE DB to staging folder
3. Run your new FE pointed at the copy of live DB
4. If it updates the BE correctly you can ship the FE to the live folder
5. The next time someone runs the FE App Launcher it copies the new FE, which updates the BE for you.

 

[The_Doc_Man]

@HackSlash - your list items 3, 4, & 5 run into the problem of what happens if you DON'T correctly update the BE when you run the new FE.

The USUAL way you do this in a shop that tries to tightly control things is you ALWAYS have a scheduled down-time during which you can run the BE update and in that case, you update a BE table that allows the FE to know if it is allowed to use that BE.

 

[HackSlash]

@The_Doc_Man Step 3 is the staging environment I was talking about. I apologize if I was unclear. If you can get it working in the staging environment then your chances of success is high. (I've had 100% success with this over the last 4 years)

You do need good communication for steps 4 and 5. I generally do the update myself but it's designed that you could ship just the new FE to a customer and let them do their own update.

If you look into the projects I cited, there is a ton of logic in those solutions to avoid problems. Including making backups and doing roll back. If the update fails you just put the old FE back in place and they can continue using the old version until you can fix the issue.

 

[Pat Hartman]

@HackSlash Welcome aboard. You can't post attachments or links until you've posted at least 10 comments. We need to ensure that you are not a bot

 

[The_Doc_Man]

@The_Doc_Man Step 3 is the staging environment I was talking about. I apologize if I was unclear. If you can get it working in the staging environment then your chances of success is high. (I've had 100% success with this over the last 4 years)

You do need good communication for steps 4 and 5. I generally do the update myself but it's designed that you could ship just the new FE to a customer and let them do their own update.

If you look into the projects I cited, there is a ton of logic in those solutions to avoid problems. Including making backups and doing roll back. If the update fails you just put the old FE back in place and they can continue using the old version until you can fix the issue.

Your method depends on the first user to find the need for the update to occur. But because structural updates take time, you run a risk if two users come in at close to the same time, in which case you would experience a type of interference - where the left hand doesn't know what the right hand is doing. It seems that you could experience either data loss or a serious error sequence based on locks that are longer-term than normal as would be the case for a simple update.

 

[HackSlash]

Your method depends on the first user to find the need for the update to occur. But because structural updates take time, you run a risk if two users come in at close to the same time, in which case you would experience a type of interference - where the left hand doesn't know what the right hand is doing. It seems that you could experience either data loss or a serious error sequence based on locks that are longer-term than normal as would be the case for a simple update.

You're making a lot of assumptions here. I already said that I do my updates myself. You really should look at the backend updater before you keep going. It doesn't proceed if the BE is open. It also opens it in exclusive mode so nobody else can access it.

If you are shipping to a customer the "user" doing the update on their end should be IT staff at the customer who manages their other software. Everyone is a user to a software developer, even experienced IT staff. This is no different than any other software update you push. It doesn't matter if the technology is Access, SQL, or a native windows app. You're going to be supporting an admin who controls the software for the organization. You can provide that person whatever documentation your application requires.

I'm not sure if this is what you are suggesting but I've never purchased a piece of software that the developer needs to install for me on my systems. That's not scalable or sensible.

 

[HackSlash]

I wouldn't sell an Access application to a customer anyways. I would rewrite the FE in .NET6\C#\WinUI3. I would have a similar BE update routine in my FE software, it would just be an .EXE instead of an .ACCDE