Wednesday, November 28, 2012

Why individual Front End Files

In a recent Microsoft Access class discussion, participants enquired behind the reasons behind splitting the database file and then having one pair of Front End and Back End files and then having individual Front End files for each user. Questions like this force one to re-inspect and itemise the reasons for this Best Practice.

You want individual Front End Files because:

  1. If a single user crashes the system or hangs their machine, the risk of corruption is most at the Front End File - the Back End file is at a much lower risk.
  2. Intense and Frequent file i/o speed as well as temporary data generated by processing data is at the Front End File, not the Back End file. If the Front End file grows big, it can be easily discarded and a new fresh one copied over.
  3. Temporary tables can be set up to use the same name, you don't have to design a system where User A uses tblTempA and User B uses tblTempB - each user can simply be programmed to use tblTemp stored privately in each Front End. There are many approaches where temp tables are a useful technique in processing data.
  4. Even without programming, you can touch a Form or Report such that settings are "dirtied" and need to be saved. For example if you move the widths of some columns in Datasheet View, that Form may need to be saved so that you can maintain that width the next time you open it.
    Table data are multi-user out-of-the-box in Microsoft Access. Forms and Reports are not.
  5. If you mix Access 2010 and Access 2007 for example, after some time, you may notice that Access 2010 designs in Forms and Reports may render the .accdb un-openable in Access 2007.
Food for Thought.