Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

762

The local .accdb also contains all the user interface objects, including forms, reports, queries,

macros, and modules. Keeping the user interface components on the local machine dramatically

improves performance. You don’t need to move forms, queries, or reports across the network —

these objects are much more easily manipulated on the local machine than when accessed across

the network.

All shared tables and tables that are changed at regular intervals really should be placed in the

back-end database kept on the server. The server database is opened in Shared mode, making all

its objects accessible to multiple users. The tables in the server database are linked to the front-end

.accdb on each user’s desktop. (There is no problem with simultaneously linking the same table

to multiple databases.)

Obviously, with more than one person using the data within a table, the possibility exists that the

same record will be edited by multiple users. The Access database engine handles this problem by

locking a record as it’s edited by a user. A lock contention occurs when more than one user tries to

update the same record. Only one user will have “live” access to the record — all other users will

either be locked or have their changes held up until the record holder is done making changes.

I explain the Access database locking mechanism in detail in the “Access’s built-in record-locking

features” section, later in this chapter

Using the Database Splitter add-in

The Database Splitter helps you split an application into front-end and back-end databases. This

wizard enables you to build and test your database to your heart’s content, and then lightens the

burden of preparing the application for multiuser access.

As an experiment, let’s take a look at splitting the Northwind Traders database into front-end and

back-end .accdb files. You start the Database Splitter by choosing the External Data tab of the

Access ribbon, and then clicking on the Move Data drop-down list and selecting Access Database.

The opening wizard screen (see Figure 21.4) explains the actions of the Database Splitter and sug-

gests that you make a backup of the database before proceeding.

FIGURE 21.4

The Database Splitter is a very simple wizard.