Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

761

FIGURE 21.3

A database kept on a file server can generate a large amount of traffic on the network.

Workstation A

Back-end

Access

database

File server

Network cable

Workstation B Workstation C

Front-end

Access

database

Front-end

Access

database

Front-end

Access

database

The network traffic is reduced in Figure 21.3 because only linking information and data returned

by queries is moved across the network. A user working with the database application uses the

forms, queries, reports, macros, and code stored in the local front-end .accdb file. Because the

front end is accessed by a single user, response time is much improved because the local copy of

Access is able to instantly open the database and begin the start-up operations. Only when actually

running queries does the network traffic increase.

The second major benefit of the design in Figure 21.3 is that updating the forms, reports, and

other application components requires nothing more than replacing the front-end database on

each user’s computer and reestablishing the links to the table in the back-end database. In fact, the

design in Figure 21.3 supports the notion of customized front ends, depending on the require-

ments of the user sitting at each workstation. For example, a manager sitting at Workstation A

might need access to personnel information that is not available to the people sitting at worksta-

tions B and C. In this case the front-end database on Workstation A includes the forms, queries,

and other database objects necessary to view the personnel information.

Knowing where to put which objects

Not all tables need to be put into the back-end database. Generally speaking, objects that rarely

change can be kept in the front-end database, while objects that change frequently should be kept

in the back-end .accdb file. Keeping tables in the local .accdb reduces network traffic — there’s

no reason to move static data such as state abbreviations, city names, and zip codes across the net-

work. The local tables require updating only when the data changes.