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.

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.

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.

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.