Exclusive lock is required for saving design changes to Access objects in Access 2000 (200290)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q200290
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

For a Microsoft Access 2002 version of this article, see 283228.

SYMPTOMS

When you try to customize toolbars or to open a Microsoft Access form, report, macro, or module in Design view, you receive the following error 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 error 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 error message:
A link to this data access page could not be created because the database cannot be exclusively locked.

CAUSE

This problem occurs when you try to open a form, report, macro, module, or commandbar in Design view. This problem also occurs when you try to save design changes to one of these object types or a new page link while other users currently have the same database open. To save design changes to these object types, Access must be able to obtain an exclusive lock on the database.

RESOLUTION

When multiple developers are designing an Access application simultaneously, you can implement source code control by using the Microsoft Visual SourceSafe Add-In for Microsoft Access. Alternatively, you can 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 permits you to put your Access application under source code control while the application is under development. When you put your application under source code control, you can track and store changes that are made to your application over time. By using Microsoft Visual SourceSafe, team members can review the history of an object and then revert to earlier versions of an object. Individual users can check out objects in the Microsoft Access application, modify the objects, or create new objects in their local copy, and then check the objects back into the main database under source code control. The Microsoft Access Visual SourceSafe Add-In is available with Microsoft Office 2000 Developer. To use the Microsoft Access Visual SourceSafe Add-In, you must also install Microsoft Visual SourceSafe, which is 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 develops their own 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, they import the object from the master database to their local working database. Then, the developer makes the required changes to the object in their database and saves the object. When the developer is ready to commit the changes to the master database, they export the object to the master database and overwrite the original object.

A disadvantage to 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 can unknowingly overwrite changes that another developer commits to the master database.

MORE INFORMATION

To save design changes to Access-specific objects, such as forms, reports, new page links, macros, modules, and commandbars, Access 2000 must 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:
  • Consistency with other Visual Basic Environment client applications is provided.
  • Dependency on the Jet database engine is stopped.
  • Stability of Access-specific objects is improved.

Consistency with Other Visual Basic Environment Client Applications Is Provided

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

Dependency on the Jet Database Engine Is Stopped

Access 2000 now offers the ability to create Microsoft Access project (.adp) files and also 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 on the Jet database engine for storage. These objects were stored in Access-specific system tables in the Microsoft Jet database. Because it is possible for Access 2000 to use Microsoft SQL Server as an alternative to Microsoft Jet, a storage mechanism had to be developed for Access-specific objects that did not rely on the Jet database engine.

Stability of Access-Specific Objects Is Improved

The new project storage model improves the stability of Access-specific objects and the Visual Basic project. Visual Basic for Applications has never allowed multiuser editing of Visual Basic projects without source code control. Microsoft Access 95 and 97 can circumvent this restriction by hiding project changes that are made in a multiuser environment from Visual Basic for Applications and then merge them into the project later. However, this has the potential for affecting the stability of the Visual Basic project. Therefore, Microsoft Access 2000 requires an exclusive lock when designing Access-specific objects to make sure that the project has only one editor.

Editing Microsoft Access Objects in a Multiuser Environment

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

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

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

Lock Promotion

To make sure there is 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 attempts 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 to prevent the scenario where a user has made multiple design changes only to later find that they cannot save them because Access cannot obtain an exclusive lock. By trying lock promotion as soon as the user opens an object in Design view, Access can warn the user if it cannot obtain an exclusive lock before the user makes any design changes. Access does not try lock promotion when opening a module in Design view. However, Access tries lock promotion as soon as the user edits any module in the database.

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

If Access cannot promote the lock to exclusive when the user opens an object in Design view, Access notifies 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 opens the object in Design view and allows the user to make design changes. If the user tries to save the object, Access tries to promote the shared lock to exclusive. If lock promotion is successful, Access saves the object and then 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 receives 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 tries to close the dirty object and to save changes, then Access prompts 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 the Behavior

  1. Start two instances of Microsoft Access on the same computer.
  2. Open the sample database Northwind.mdb in both instances.
  3. 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.
  4. Click OK to clear the message. Note that the form opens in Design view.
  5. Add a text box control to the form.
  6. 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.
  7. Click OK to clear the message.
  8. Close the second instance of Access on your computer.
  9. In the first instance of Access, try 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, click the following article number to view the article in the Microsoft Knowledge Base:

198756 How to use Connection Control to prevent user log on at run time in Access 2000

Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbdesign kbsavefile kbopenfile kbDAP kberrmsg kbprb KB200290 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.