The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
March 2008

Splitting A Database: Create a Front End & a Back End

The typical Access user creates a database as a single file because it is the default setting offered by Access; and this makes sense if only one person uses the database.

There are however, some compelling reasons to NOT create a database as a single file.

If you share a database with other users, or if you use a database across a network, it's a good idea to split your database into two pieces-a front end and a back end.

In this Issue
  • Tip of the Month-Create a Shortcut to a Favorite Report
  • The Terminology
  • Why Split Things Up?
  • How To Split An Existing Database
  • The Road Forward

  • The Terminology

    A database is split into a front end and a back end. The backend contains only data - think nothing but tables. The front end contains forms, reports, queries, code, infrastructure and macros (as an aside, if you've been reading the wizard for while you know that I'm not a fan of macros). The front end basically contains everything in your application that's not data.

    Why Split Things Up?

    As mentioned in the introduction, there are definite advantages gained by separating your database into two pieces, especially when a database is shared by multiple users. The first is that isolating your data tables into a separate file allows you to make code changes to the forms and reports while other users are updating the database.

    The second is if you are updating a shared database that is split into two pieces, you can simply make modifications the front end (forms, reports, queries, and codes) and save it over the existing application without touching the data. This way you can make improvements transparently to your users, who still have the ability to use the database and update data.

    Lastly, if a database is stored on a network, there are performance advantages to storing the front end on a local machine and the back end on a network drive. Saving the front end on a local machine will reduce network traffic. The typical application makes frequent calls to forms, reports, and code and periodic calls to get pieces of data; by keeping your forms and reports locally, you'll get a faster application.

    How To Split An Existing Database

    A database in a single file is not difficult to separate into two pieces, in fact Access provides a wizard dedicated to help you with this. Activate the wizard under the tools menu at the top of the screen; choose Tools > Database Utilities > Database Splitter. The wizard will walk you through the steps to move your data tables into a separate back end file. You'll first be asked to name your back end file. The default choice will be the current file name of the database with the characters "_be" (shorthand for "back end") appended at the end of the file name. This is a standard designator for a data file and I recommend you accept the default. The wizard will also ask you where you want to save the newly created back end. If you're working on a network and want other users to access the data, you have the option of saving to a network drive.

    When you click OK, the wizard will take all the tables in your database and move them into your newly created back end file. Next it will create a link from the tables in your back end file to the front end file. It'll do this automatically, so it's a fairly simple and painless process.

    Please note that your tables now have an arrow symbol next to them. This indicates a linked table- basically a table that does not exist in your current database but rather is linked to from a different source. In this case it's a very simple link, just one Access database linked to another Access database. This hints at the possibilities of linking from a variety of sources -- more about that in future issues.

    The Road Forward

    At this point the database is now split into a front end and a back end and you can take advantage of the benefits offered by this arrangement, especially if you're sharing a database between users. Be aware, however, that there is a cost to splitting the database, as you are now charged with managing the linkage between these two files.

    Next month I'll review the process for re-linking tables. It's not something that you will have to do as a standard procedure, however it's important that you know how to do it so that when something changes in your system you'll be able to re-establish the pointers.

    Tip of the Month-Create a Shortcut to a Favorite Report

    If you find yourself calling a specific report repeatedly, Access has a really nifty little trick to make this task easier: creating a shortcut to the report.

    The first step is to minimize all applications so you can see the desktop. Next, open your Access application and click on the tab or page that contains your report list, next shrink the size of your Access application so that you can see both the report list and your desktop.

    Left click any report from the list and drag it to your desktop. Windows will create a link to your report on your desktop. So the next time you to need to run the report, simply click on the link and it will automatically open your application and then run the report.

    You can place this link not only on the desktop but also on your Windows menu, on your shortcut bar, or in any place that can host a Windows shortcut link. This trick is not limited to reports; it can also be used with forms, tables, or queries.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886