Opening a database with linked tables exclusively

[cmcsween]

Is there a way in Access 2007 to open a database with linked tables exclusively when the other database containing the tables is also open? I was told by the user that this could be done in Access 2000, but I think it is a security restriction in Access 2007. Thank you.

Carol McSween-Brooks

[Stewart Ross]

Hi Carol. I suspect you've been misinformed about A2000, and that A2007 does what other versions of Access have done for many years.

If an Access database is open in non-exclusive mode then it cannot be opened exclusively by anyone else.

When a connection is made to tables in the current database, or to linked back-end tables in another Access database, Access creates a lock file named after the database concerned but with extension .LDB. It uses this to record users currently accessing the database. If a user is active in using linked tables then the LDB file will show that they have access to the database concerned. Until the lock file is cleared there is no way that another user can grab exclusive use of the database.

Once all other users have exited it is of course possible to open a database in exclusive mode - but then no-one else will be able to access it until the exclusive lock is removed.

-Stewart

[cmcsween]

Thank you, Stewart. It didn't seem like a good idea to have the linked tables open by more than one user if changes were being made to them - even if they weren't to the same records.

Carol

[Stewart Ross]

Hi Carol. Databases are inherently multi-user, so more than one person editing records at the same time is not a problem. What can be is how to handle more than one person editing the same record at the same time. By default, Access adopts an optimistic locking strategy, where it will only apply a lock to the current record (lock meaning denial of edit access) at the last moment, as it tries to save the changes. It will warn any other user that the record has been changed by a different user.

This lock strategy can be changed if required, so that Access locks a record from the moment a user starts to edit that record.

This would avoid a possible problem with optimistic locking, which is cascaded edits overwriting each other (one user saving changes, which are immediately overwritten by another user).

Exclusive access is more about allowing for design changes or system maintenance such as backup, as this access mode effectively makes the database single-user only. As it denies all other users access it is not normal to open a database in exclusive mode except for maintenance purposes.

-Stewart