ACC2000: Exclusive Lock Required for Saving Design Changes to Access Objects

ID: Q200290

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you try to customize toolbars or open a Microsoft Access form, report, macro, or module in Design view, you receive the following message:

You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.

When you try to save design changes that you made to an Access form, report, macro, or module, you receive the following message:

You do not have exclusive access to the database at this time. Your design changes will not be saved.

When you try to save a new data access page, you receive the following message:

A link to this data access page could not be created because the database cannot be exclusively locked.

CAUSE

You are trying to open a form, report, macro, module, or commandbar in Design view. Or you are trying to save design changes to one of these object types or a new page link while other users currently have the same database open. In order to save design changes to these object types, Access must be able to obtain an exclusive lock on the database.

RESOLUTION

In situations where multiple developers are designing an Access application simultaneously, you should implement source code control using the Microsoft Visual SourceSafe Add-In for Microsoft Access. Or you should distribute local working copies of the database to each developer. A discussion of each of these options follows.

Implementing Source Code Control

The Microsoft Access Visual SourceSafe Add-In allows you to place your Access application under source code control while it is under development. Placing your application under source code control allows you to track and store changes made to it over time. By using Microsoft Visual SourceSafe, team members can review an object's history, revert to earlier versions of an object, and develop applications concurrently. Individual users may check out objects in your Microsoft Access application, modify them, and check them back in. The Microsoft Access Visual SourceSafe Add-In is available with Microsoft Office 2000 Developer. In order to use the Microsoft Access Visual SourceSafe Add-In, you must also install Microsoft Visual SourceSafe, which is also available with Microsoft Office 2000 Developer, separately.

Using Individual Working Databases

Another option you can implement is to keep a master copy of the database application in a centralized location, and then use individual working copies of the database on each developer's computer. Each developer would develop his or her portion of the application in the local working copy of the database. When the developer wants to make a change to an object in the database application, he or she would import the object from the master database into their local working database. Then the developer would make the necessary changes to the object in their database, and save the object. When the developer is ready to commit the changes to the master database, he or she would export the object to the master database, overwriting the original object.

One disadvantage of using this approach is there is no way to determine if multiple developers are concurrently working on the same object locally. When the developer exports the object to the master database, the developer could unknowingly overwrite changes that another developer has committed to the master database.

MORE INFORMATION

In order to save design changes to Access-specific objects, such as forms, reports, new page links, macros, modules, and commandbars, Access 2000 must be able to lock the database exclusively during the Save operation. Tables, queries, and relationships do not fall under this restriction because they are Microsoft Jet-specific objects. Microsoft introduced this requirement to Access 2000 for several reasons:

It provides consistency with other Visual Basic Environment client applications.
It eliminates dependency on the Jet database engine.
It improves stability of Access-specific objects.

Provides Consistency with Other Visual Basic Environment Client Applications

Because Access 2000 now hosts the Microsoft Visual Basic Environment, the save model used by Microsoft Access must be consistent with other applications that host the Visual Basic Environment. The Microsoft Visual Basic Environment only allows exclusive editing and saving of Visual Basic projects that are not under source code control. This is true of Microsoft Visual Basic 6.0, as well as all Microsoft Office applications that host the Visual Basic Environment.

Eliminates Dependency on the Jet Database Engine

Access 2000 now offers the ability to create Microsoft Access project (.ADP) files as well as Microsoft Access databases (.MDB). Using an Access project offers developers the ability to use Microsoft SQL Server as an alternative database engine to Microsoft Jet. In the past, all Access specific objects (forms, reports, macros, modules, and commandbars) were dependent upon the Jet database engine for storage. These objects were stored in Access-specific system tables within the Microsoft Jet database. Because it is possible for Access 2000 to use Microsoft SQL Server as an alternative to Microsoft Jet, Microsoft had to develop a storage mechanism for Access-specific objects that does not rely on the Jet database engine.

Improves Stability of Access-Specific Objects

The new project storage model improves the stability of Access-specific objects and the Visual Basic project. Visual Basic for Applications has never allowed multi-user editing of Visual Basic projects without source code control. Microsoft Access 95 and 97 were able to circumvent this restriction by hiding project changes made in a multi-user environment from Visual Basic for Applications, and merging them into the project at a later time. However, this had the potential for impacting the stability of the Visual Basic project. Thus, Microsoft Access 2000 requires an exclusive lock when designing Access-specific objects to ensure that the project has only one editor."

Editing Microsoft Access Objects in a Multi-User Environment

Because users may open a database either for exclusive or shared use, the save behavior exhibited by Access depends on how the user opened the database, and if multiple users are currently accessing it.

If a developer opens the database for exclusive use, the developer will be able to save the design of any Access-specific object, provided that the developer can open the database for read/write access and has sufficient permissions to modify the design of the object.

If a user opens the database for shared use, the user will be able to save the design of any Access-specific object, provided that the user can open the database for read/write access, has sufficient permissions to modify the design of the object, and Access can obtain an exclusive lock on the database.

Lock Promotion

In order to ensure exclusive use of the database, Access uses the connection control feature of the Jet database engine to promote the user's shared lock to exclusive. Access will attempt to promote a shared lock to an exclusive lock as soon as the user opens a form, report, macro, or commandbar in Design view. Access attempts lock promotion at this time in order to prevent the scenario where a user has made multiple design changes only to later find that the user cannot save them because Access can't obtain an exclusive lock. By attempting lock promotion as soon as the user opens an object in Design view, Access can warn the user if it can't obtain an exclusive lock before the user makes any design changes. Access will not attempt lock promotion when opening a module in Design view; however, it will attempt lock promotion as soon as the user edits any module in the database.

Access will maintain the exclusive lock until the user saves or discards all dirty objects and no other objects are open in Design view. Following this, Access will demote the lock back to shared if the database was originally opened for shared use.

If Access is unable to promote the lock to exclusive when the user opens an object in Design view, it will alert the user with the message:

You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.

Following this warning message, Access will open the object in Design view and allow the user to make design changes. If the user attempts to save the object, Access will attempt to promote the shared lock to exclusive. If lock promotion is successful, Access saves the object and maintains the exclusive lock until the user saves or discards all other dirty objects and no object remains open in Design view. If lock promotion fails, the user will receive the following message:

You do not have exclusive access to the database at this time. Your design changes will not be saved.

If the user attempts to close the dirty object and save changes, then Access will prompt the user with the option of closing the object and discarding design changes made to it, or leaving it open and unsaved.

Steps to Reproduce Behavior

Start two instances of Microsoft Access on the same computer.

Open the sample database Northwind.mdb in both instances.

In the first instance of Microsoft Access, open the Customers form in Design view. Note that you receive the message:

You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.

Click OK to clear the message. Note that the form opens in Design view.

Add a text box control to the form.

On the File menu, click Save. Note that you receive the following message:

You do not have exclusive access to the database at this time. Your design changes will not be saved.

Click OK to clear the message.

Close the second instance of Access on your computer.

In the first instance of Access, attempt to save the form again. Note that the form is saved successfully.

REFERENCES

For additional information about the connection control feature of the Jet database engine, please see the following article in the Microsoft Knowledge Base:

Q198756 ACC2000: Use Connection Control to Prevent User Log On at Run Time

Additional query words: DAP save model savemodel

Keywords : kberrmsg kbdta AccDAP
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb