Splitting MS Access Database - Front End Part Location

Question

One of the best practices as specified by Microsoft for Access Development is splitting Access application into 2 parts; Front End that hold all the object except tables and the Back End that holds the tables.

The msdn page links there to the article Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability that describes the process in details.

It is recommended that in multi user environment the Back End is stored on the server/shared folder while the Front End is distributed to each user.

That implies that each time there are any changes made to the front end they need to be deployed to every user machine.

My question is:

Assuming that the users themselves do not have rights to modify the Front End part of the application what would be the drawbacks/dangers of leaving this on the server as well next to the Back End copy?

I can see the performance issues here, but are there any dangers here like possible corruptions etc?

Thank you

EDIT

Just to clarify, the scenario specified in question assumes one Front End stored on the server and shared by users.

I understand that the recommendation is to have FE deployed to each user machine, but my question is more about what are the dangers if that is not done.

E.g. when you are given an existing solution that uses the approach of both FE and BE on the server. Assuming the the performance is acceptable and the customer is reluctant to change the approach would you still push the change? And why exactly? For example the danger of possible data corruption would definitely be the strong enough argument, but is that the case?

---

It is a part of follow up of my previous question From SQL Server to MS Access 2007

Answer 1

To keep the front-end on the server would more or less defeat the purpose of splitting the database. Putting the front-end on the desktop reduces network traffic since the application is not retrieved for each use, and allows the front end database to contain tables with data that is private to each user for storing settings or temporary data.

If you wish to avoid data corruption, it is important that each user should have their own copy of the front-end. Allen Browne offers more details on avoiding corruption in this article

There are a number of utilities available to update the front-end version on the desktop as required, or you can even write such a utility yourself.

Answer 2

The only drawback to leaving the individual user specific copies of the FE on the server is network performance. It won't make a difference as far as data corruption.

But you shouldn't share a FE between multiple users. This is prone to corruptions on the FE and other weirdness. Each user should get their own copy of the FE. Also you can't replace it with a new copy while users are using it.

A client was running for years with the FE on individual user folders on the file server but running msaccess.exe in a Citrix cluster. The IT staff didn't want to have anything updating the local hard drives of the Citrix cluster server systems.

As far as deploying the FE see the Auto FE Updater at my website. Huge changes coming in the next week to make it much, much easier for both initial server install and easier user initial install.

Answer 3

I agree with the others. Keeping the fe on the server is not recommended. Just put a batch file on your server that does the push. When you have an update send a shortcut to the batch file via email. That is one of many solutions. Once you set it up it is not a problem.

Seth

Answer 4

As an Access 2007 Programmer using a Front End (FE) that is linked to a Back End (BE) database (a.k.a. Split Database) I have done both of the above. Sending an updated FE to users has other overhead, esp if third party controls or applications are used.

As for Citrix, back in Access 97 days, a Citrix manager was able to allow me to put one copy of the FE in a server file location. It would create a new instance for each user that logged in. We were able to use over 50 users with out any impacts. I must qualify this by saying the Access VBA code used efficient updates and transactions with roll-backs rather than just simple Select statements.

My problem today is Access 2007 running on a Citrix server (Windows 2003). When I am the only person logged into Citrix, the application ( I picked a large complex report that creates a custom Excel spreadsheet via automation for the test) it runs within 1% as fast as running the FE from my XP workstation, and linking to the BE on the Citrix server hard drive.

But, when two or three people log into the Citrix Server, the same report takes three times as long. However, while two or three people are logged into Citrix, I can run my FE from my XP workstation and it runs exactly like the single-user on citrix.

A FE posted on a shared networked drive, shared by two or three users is NOT advised for this same reason. Access FE are not designed to be shared (* I will spare the details*). That is why people put a FE on each workstation and share one database (BE).

What I find lacking in Citrix is some good step-by-step "how-to" run Access FE on Citrix. Ideally, a single file could be posted. When a user loggs into Citrix, Citrix should make a copy of the FE and assign the resources (for Access) to that user's login. I think this is exactly what MS Office does automatically or at least has instructions on how to do it.

If such a document exist, please post it. A programmer like myself would love to hand it to the Citrix Administrator. It would solve a lot of problems.