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
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
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
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
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.