|
|
|
 the
Access Experts: Custom Database Solutions
|
|
|
|
|
|
|
|
|
|
Why Split a Database? |
There are a number of
reasons for splitting an Access database into a front-end and a back-end, where the
back-end contains only tables, and the front-end contains queries, forms, reports
and modules. More or less in the order of impact, they are:
-
Significant performance benefits can be achieved if the back-end is on a server and
the front-end is located on the local hard drive of the users’ PC. This arrangement
loads forms and reports from the local hard drive, which is almost always faster than
loading them from a LAN connection to a server, and it also reduces the traffic on
the LAN by only sending the requested data, and not forms and reports. Note however
that the data passed is the same, as all query activity occurs on the local PC. To
gain further performance benefits in this area, you should consider a database server
such as SQL Server or Oracle.
-
Updating or adding to the design of queries, forms, reports and modules is made easier,
as you can replace the front-end on each PC on an as-needed basis. Otherwise, in a
shared database, all users should exit the database before you make design changes.
With Access 2000 and beyond, all other users must exit the database before you make
changes to forms, reports or modules.
-
Using local front-ends will provide a more robust solution. Most corruption in databases
occurs in the forms, reports and modules objects. Thus a local front-end that goes
corrupt only affects that specific user, and can be replaced without requiring all
users to exit the database.
-
This arrangement makes it easier to debug problems experienced by only one, or a few
users, and not by others. Individual
users often do tasks or use procedures that others don’t, and it becomes easier to
narrow down the specific causes of problems. If
all users are running from a common database, this can be a very difficult situation
to sort out, as one user may cause a number of users to have problems, and it isn’t
clear who caused the initial problem.
-
This arrangement allows users to create local objects in their own copy of the front-end
without cluttering up the environment for other users. Please
note that early versions of the switchboard manager required that the switchboard
table be a local table – the situation with Access 2000/2002 doesn’t appear to be
documented.
-
Users can’t accidentally (or deliberately) delete a table in this configuration –
they can only delete the link to the table. Of course security can be implemented
to prevent that as well, but the bottom line with an Access database is that all users
must have edit permissions on the .mdb file, which in some cases means they could
theoretically delete the actual file. Backup and do it frequently is the bottom line.
-
This configuration makes it easy to switch between a test version and a live version
of the database if you need to verify design changes or do training for a new user.
The linked table manager can be used for this, or you can write code to do your own
relinking.
-
If you have a situation where some people are running Access 97 on their workstation
and others are running Access 2000 or 2002, you can use an Access 97 back-end database,
and do a version of the front-end in Access 97 for people with that release, and a
second front-end version in Access 2000 for people running 2000/2002. Also
note that Access 2002 can run an Access 2000 databases in native mode with no apparent
performance penalty.
How to split a database?
Splitting a database is a fairly easy task – in fact Microsoft provides a wizard as
part of Access that will do the job for you. You
might want to take that approach the first time you do it. The
wizard can be found under the Tools
/ Database Utilities / Database Splitter and walks you through the process. If
you want to do it manually there are two processes you can use. The
first is:
-
Make a copy of the database!
-
Delete all the tables from the copy
-
Delete all the queries, forms, reports, macros and modules from the original
-
In the copy, use the Linked Tables Manager to link to all the tables in the original
-
Compact and repair each database
Of course you should make a backup of the database before you start. The
second process is:
-
Create an empty database (the back-end)
-
Import all of the tables into it
-
Create a second empty database (the front-end)
-
Import all of the queries, forms, reports, macros and modules into it
-
Use the Linked Tables manager to link to all the tables in the back-end
This process has the advantage of providing a back-up, and may clean up a few problems
in the process, resulting in smaller sizes for both databases, so we typically either
use the wizard or this second process.
Are there downsides to splitting a database?
As with most technology options, there are some aspects of split databases that
will raise issues in certain circumstances. The
significant ones are:
-
Copying a database to a workstation can be a tedious,
and deploying frequent design changes are likely to make it an even bigger burden. A
common solution is to use a small batch file to copy the front-end to the workstation
hard drive each time the workstation is rebooted, and that works well for small networks
with 5 to 10 users.
-
However, replacing the front-end on 50 or 100 workstations can become a burdensome
task, and can create a significant load on the network. We
have developed a tool known as DBLauncher that
automates the task of downloading both databases and components, and will work with
multiple databases. Also, if multiple
developers are working on a given front-end, you should seriously consider using Microsoft
Visual Source Safe or another source control system.
-
When working with an Access 97 back-end connected to an Access 2000/2002 front-end,
you should be aware that Access 2000 users will see a noticeable decline in performance
compared with Access 97 users. There is also anecdotal evidence that corruption problems
in the back-end may occur more frequently in this configuration. This
should be used as a temporary expedient until all workstations are converted to Access
2000.
-
When users are allowed to create local objects in the front-end, considerable care
and planning is required for maintenance updates, as local objects will normally be
lost when the front-end is replaced.
What’s the bottom line?
In summary, we recommend that you always use a split database approach, even if you
are a single user working on a local hard drive because, of performance improvements,
the reduction in problems with corruption that are caused most often by hardware or
software crashes, and the improved development conditions.
|
|
|
|
Copyright © 1998-2011 Wendell Bell & Associates
Inc. Last updated on August 2, 2011
Email protected by MX Guarddog spam filtering
|
|
|
|