Chapter 21: Building Multiuser Applications
759
Splitting a Database for Network Access
One common technique employed by many experienced Access developers working in multiuser
environments is splitting the database into front-end and back-end components. This relatively
simple operation can yield big benefits in terms of networked application performance and future
maintenance on the application.
Detailing the benefits of splitting a database
There is at least one extremely good reason why you should consider splitting your Access data-
bases. Although you can place a single copy of an .accdb or .mdb file onto a shared computer on
the network, the performance degradation from such a design is considerable.
Using an Access database stored on a remote computer involves much more than simply moving
data from the remote computer to the local machine. All the form, menu, and ribbon definitions
must be transported to the local computer so that Windows can “construct” the user interface on
the local computer’s monitor. The Windows installation on the local computer must intercept and
transmit any keyboard and mouse events to the remote computer so that the proper code will run
in response to these events. Finally, the single copy of Access on the remote computer must fulfill
all data requests, no matter how trivial or demanding. The impact of all these actions is com-
pounded by increasing the number of users working with the same remotely installed copy of the
database.
Fortunately, most of these issues disappear when the database application is split into front-end and
back-end components. The local Windows installation handles the user interface from information
stored in the front-end database. All code is run on the user’s desktop computer, rather than on the
remote machine. Also, the locally installed copy of Access is able to handle all local data require-
ments, while only those requests for remote data are passed on to the back-end database.
Before getting into the details of splitting a database, let’s consider some of the problems associated
with single-file databases. To begin with, unlike some other development systems, all the objects in
an Access database application are stored in a single file, the familiar .accdb or .mdb you work
with every day. Many other database systems like FoxPro for Windows maintain a number of dif-
ferent files for each application, usually one file per object (form, table, and so on). Although hav-
ing to deal with multiple files complicates database development and maintenance somewhat,
updating a single form or query involves nothing more than replacing the related file with the
updated form or query file.
Updating an Access database object is somewhat more complicated. As you’ve probably discov-
ered, replacing a form or query in an Access database used by a large number of users can be quite
a problem. Replacing a form or other database object often requires hours of work importing the
object into each user’s copy of the database.
A second consideration is the network traffic inherent in single-file Access databases. Figure 21.2
shows an example of the problem. This figure illustrates a common method of sharing an Access
database. The computer in the upper-left corner of the figure is the file server and holds the Access