Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

764

Figure 21.6 shows the Access Database Explorer after splitting the Northwind Traders database.

The back-end database only contains the tables exported from Northwind.accdb. Notice that

the icons associated with all the tables in Northwind.accdb have been changed, indicating that

they are now pointing to copies in the back-end database. You’ll have to import any local tables

from the back-end database before distributing the front end to the users.

FIGURE 21.6

The Database Splitter creates links for all tables in the database.

Finding the Key to Locking Issues

In multiuser environments, one of the most nagging problems involves a situation where two or

more users try to access the same record at the same time. Although Access lets you alter the way it

locks records using built-in options, the best cure for record-locking problems is a combination of

Access’s record-lock settings, careful planning, and error-handling procedures.

Access has long been criticized for its handling of record locks. Some of the older ISAM (Indexed

Sequential Access Method) databases, and even some high-end server products, give you the ability

to lock an individual row within a table or recordset. The advantage of this individual record-lock

ability is its certainty: You know that only one person can access a record at a time. You can easily

code procedures to handle single-record locks. In systems using record-locking schemes, it’s easy

to know which individual record is locked and handle lock contentions against that one record.

Access uses either row-level (record) locks or page locks. A page is a 4K-size section of your table

that Access pulls into memory when you want to change a record within a table. When using page

locking (which is a good option if performance is an issue), Access locks the entire 4K page con-

taining the record. If the record is greater than 4K, Access locks as many 4K pages as necessary to

lock all the record’s data.

Page locking makes more efficient use of system resources by caching data locally, allowing you to

have more responsive applications. The page-locking scheme in Access also corresponds to the

locking behavior of high-end client-server database engines like SQL Server. After you’ve mastered

the page-locking mechanisms in Access, you’ll have a much better understanding of how locking

works in client-server environments.