AccessDiscussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.

Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .

All:
Clerk A enters record 1 on a form bound to access table and then goes on to enter more records on the same form. Clerk B tries to look up record 1 by a non-key field on the form (say, last name) to enter additional data--she gets a "not found" even though Clerk A is no longer on record 1. Why? Record locking is set to "edited records." I have tried a DoCmd.Save acTable "TableName" on the "after update" event of the form and this doesn't work either. Your solutions or suggestions would be greatly appreciated.

Any time you consider the split database design in Access (the back-end MDB residing on a file server and the front-end MDB on a user's computer), it presumes linked tables in the front-end MDB file. The data tables remain in the back-end database on the file server.

It's a bad thing if multiple users simultaneously update the same record in a back-end table. Consider what would happen if two users (let's say Henry and Rocky) made changes to the Alfreds Futterkiste record at the same time. The problem isn't that the data would become scrambled. The real issue is that you wouldn't know who (Henry or Rocky) "won" and got their changes into the database.
Any time data is indeterminate, the database is considered corrupt, and you can't trust the data. Therefore, you rely on the database engine (Jet, in the case of Access) to avoid data corruption caused by simultaneous updates.

Jet and all other database engines handle this situation by imposing locks on records as they're edited. A lock notifies the engine that someone is editing the record. It's up to the database engine to appropriately respond to the lock, which prevents data corruption.

It's important to know that no database processing ever takes place on the back-end computer. Access isn't a client-server database engine. Instead, it uses a simpler file sharing approach to allow multi-user access. The back-end MDB is no different than a Word or Excel file located on a file server. All the processing takes place on the user's computer hosting the front-end MDB file.

This means there's no way for a copy of Jet running on a user's computer to know for sure what other users are doing with the back-end data. Without some way for separate copies of Jet to communicate with each other, there's no way to prevent data corruption.

The mysterious .LDB file

Everyone has seen the famous .LDB that accompanies an Access MDB. Normally this file is absent, and only appears as soon as a user requests data from the .MDB file. As a user opens
a form or report linked to the back-end database, his copy of Jet creates the .LDB file, and makes entries in the LDB notifying other copies of Jet of the user's activities.

The .LDB file includes information about the user, his computer name, and the records the user has open. As other users open the back-end MDB, their copies of Jet consult the .LDB file to determine which records are being edited.

By the way: The user's name stored in the .LDB file is his Access user name, not their network login name. This means if you don't have security applied to your application, all users have the same name (admin) and some of the multi-user issues described in this article don't apply. It also explains why you may never have had lock contention problems until after you implemented security in your applications.

As a user quits the application, his copy of Jet removes his entries as the application shuts down.

The alternative to using the .LDB file is to record user activity within the back-end .MDB file. Because of the inevitable bloating and contention issues that would occur, Microsoft wisely chose the LDB architecture instead.

Types of locks

Access supports three types of locks: optimistic, pessimistic, and table locks. Without going into a lot of details, these locks can be described as:

Optimistic: The record is locked only at the instant data is written to disk. This type of locking is called optimistic because you seldom expect to see bad things happen from multi-user interaction.

Pessimistic: The record is locked from the moment the user begins editing data until the record's changes are committed. This lock is called pessimistic because it would be a bad thing if users constantly overwrote each other's work.

Table locks: All records in the table are locked.

Optimistic locking is the most common type of lock applied in business applications. Optimistic is most often applied where updates to existing records are carefully controlled or rarely done, such as a phone bill. Two users can simultaneously edit records in the same table, as long as they're not working on the same record. Problems arise only when two users edit the same record at the same time.

Pessimistic is more appropriate in environments where changes to existing records are frequent, and the chance that two users are simultaneously writing to the same record are high (for example, hotel reservations). When you apply a pessimistic lock, anyone else trying to edit a record that exists on the same page (see the next section of this article) is locked out.

Table locking is applicable when you have to perform bulk changes, such as updating all the area codes in a phone numbers table. No other user can add or modify records while a table lock is applied. In fact, no other user can apply any kind of lock (optimistic, pessimistic, or table) while a table lock is in effect.