Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 26th, 2004, 02:34 PM
Authorized User
Join Date: Aug 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Record sharing by multiple users

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.

Old August 26th, 2004, 08:17 PM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

Information for you:
Access Record Locking Basics
Advisor DevCon speaker Mike Groh explains why Access needs record locking, and the types of locks it supports.

By Mike Groh, Technical Editor of Access-VB-SQL Advisor Magazine

11 September 2003
Length 0.5 page
Doc # 13043

3 reader comments...

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple users running the same report rashi Access 5 April 25th, 2008 07:48 AM
Multiple Users logging on feets Access 6 November 14th, 2007 11:11 AM
Multiple Users SpyderSL Access 1 December 20th, 2006 08:40 AM
Sharing a data structure across multiple threads ravishakya Pro Java 1 October 9th, 2006 08:32 PM
A Best Way To Record Track Of Users Login yoord BOOK: ASP.NET Website Programming Problem-Design-Solution 1 July 3rd, 2004 08:39 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.