Splitting an Access Database

Question

I know it's good design practice to split an Access application into front end (FE) and back end (BE) databases; however, I find maintaining the databases to be more work than necessary when implementing applications this way.  I have applications running on PCs, laptops, and MS Surface Pros.  BE modifications wouldn't be an issue; however, if I have to change the FE, I would have to install it on every computer running the application.  What's the most efficient way to make changes to the FE and distribute the changes if the database is split?  Without a split database, I can make most changes to FE objects within minutes.

Eric

Answer 1

There are indeed several tools to distribute new frontends as needed. However...

A split Access database requires - ABSOLUTELY REQUIRES - a fast, stable, noisefree connection between frontend and backend. It's very demanding of network resources. I would not recommend using a split database over any wireless connection, particularly not over a VPN. This has nothing to do with distributing the frontend; this applies to using the database. The risk is irreversible corruption of everyone's data in the shared backend.

In your environment you should really consider using a true client-server architecture, or a Web application.

Answer 2

Scott,

I understand and conceptually agree with all of the reasons for splitting a database; however, when done practically it can create issues when it's time to update the FE.  To respond to your other points:

1.  I obviously have to take into account data synchronization issues, but that's also a function of which data sources you use, e. g. internal tables or linked tables via ODBC.  The point, though, is that it all involves no work or effort by the user and doesn't involve additional components.

2.  Obviously any changes require testing and can involve intensive work.  The point again, though, is that the change is quick and immediate without the need to run additional software to implement the change on a user's computer.

3.  There are plenty of things that can go wrong in a client-server environment involving PCs and laptops/tablets that get used wirelessly to access corporate networks.  These are just a few of them:

a.  There could be a bug in whatever software is used to download the FE changes.

b.  The internal network may encounter a problem or go down.

c.  The wireless network may encounter a problem.

d.  If a VPN is used, there may be a problem.

e.  Being the unpredictable gremlins that they are, users may do something unexpected.

Granted, all of these things can happen in a non-split database, but at least it's contained, consistent, and only involves access to one file.  The complexity of a split database on multiple computing platforms creates a greater number of possible problems.  Also, corruption can be a problem even if the database is split.

The point of my question wasn't to get into a debate about the benefits of splitting a database.  The benefits are well known, documented, and understood.  I just wanted to find out if anyone had creative ways to distribute FE changes as easily as it is to implement changes to a non-split database.

Eric

Answer 3

Reasons:

1. If you make changes, you have to shut down the app when you deploy the update to make sure changes aren't lost
2. A shared app is an invitation to corruption
3. A split database makes testing easier
4. Using a local front end provides better performance and limits network traffic. Also it isolates problems.

" I make my changes and then drag/drop the file onto the server.  Done! "

But you are not done. If you have been making changes to an unsplit file then the data in that file is now out of sync with the production version, unless you have shut down the app while you were making the changes. So, before deploying the new version you have to make a copy of the data and update your new version with that data while stopping access to the app. (See point 1)

That alone is a reason to work with split databases.

My company uses a similar setup. We use the AutoFEUpdater that Bill mentioned. I have code in all my apps that tests the folder the app is opened from. If its opened from a Dev or Test folder it automatically links to a BE in the same folder. Otherwise it links to the production back end. This process has been in place almost from the day I started here three years ago. And has worked seamlessly ever since. The benefits far outweigh the drawbacks.

"Without a split database, I can make most changes to FE objects within minutes."

I challenge that statement. While I agree its certainly possible to make some changes in minutes. More often than not changes involve testing and may require some extensive time to ensure they work. I can do the same for some changes. For example; yesterday my shipping department informed me we had missed updating a contact e-mail address on a report. I opened my dev copy, made the change and uploaded the new version, all in a space of minutes. When a user goes to launch the app the next time, the new FE will be downloaded to their local drive transparently to the user. Since the data is unaffected, there is no problem is other users entered or edited data in the meantime.

Frankly I'm not seeing your concerns about things going wrong.

Answer 4

Bill,

Nice website and nice article!  However, your article proves my point.  I had thought of similar options and when everything works as it should, it's a beautiful thing.  The problem is that the process relies on scripts, hardware, the components of network communications, and those unpredictable users who do unpredictable things.  If anything goes wrong with any component, the process breaks down.  With a database that's not split, the process relies on what I do.  I make my changes and then drag/drop the file onto the server.  Done!  If I have a problem on my PC or with my network connections, I'm the only one affected and the users just have to wait a little longer for the update.

Answer 5

Eric - it really is a simple task to keep your clients updated with your latest FE. I have almost 100 different applications that I constantly am adding features/functionality to, and they are all split. See my article at http://www.thatlldoit.com/Pages/howtosarticles.aspx