Splitting your Microsoft Access MDB into a front end and back end - Why?

[ Main | AccessSplitting ]

(Updated 2010-09-27)

The most important reason is to allow you to make changes to the front end (FE).   Your users each get their own copy of the FE, likely in MDE format.  You make changes on your "master" copy of the MDB and, once complete, you make it available to the users.  (Note this use of the term "master" is not the same as replications master.)   You also don't want to use replication as it was never designed for to replicate objects but only tables and data.

You can get weird problems happening which we never took the time to figure out. If I was working on a form, in Access 97, making changes in the FE on the server then the users might get "the form has been modified since you opened it : do you want to overwrite it or save your form ?".  But they couldn't save it with the original form name so they had to save it with a suffix.  This is a very distressing message for some users.  <smile>

Access 2000 and newer don't allow multiple people to change objects at the same time.  Sometimes changing the filter or recordsource of a form is enough to make Access think you are changing the form. 

Corruptions are much more likely to happen in Access 2000 and newer in shared MDBs or shared FEs.   I've had clients where the only corruption happened in the FE because, somehow, someone had managed to point the client PCs shortcuts to the MDE FE on the server. A97 was much stabler in this.

Of the three corruptions one client had in two years, two occurred within the first few months while I was working on the combined MDB on the server. Once we split and distributed the MDE we've only got three corruption in the two and a half years since.

Note that if you want to make changes to the BE, i.e. the tables or relationships, you will need to wait until the users are no longer using the table.  Which likely means after hours.

Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment also mentions this along with some other useful tips.  Note that these also apply to versions of Access as far back as 2.0.


[ Splitting | AccessMain ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright 1995-2013 Tony Toews