About saving design changes to a shared Access database

You cannot save design changes to a Microsoft Access database while other users have the Access database open. The only way to ensure that changes are saved is by opening the Access database in exclusive mode.

In general, when you try to make a design change to a database object (except tables and queries) or an item in shared mode, Access will temporarily promote you to exclusive mode for the Access database if you are the only user of the Access database at that time. When you save all your design changes and close all the Design view windows, Access returns the Access database to shared mode. In the interim, other users will not be able to open the Access database.

If another user has the Access database open in shared mode and you try to make a major design change, such as modifying a form, Access alerts you that you might not be able to save your changes. However, if another user has the Access database open in shared mode and you try to make a minor design change, such as changing printer settings, Access doesn't alert you that you might not be able to save your design changes. In both cases, you might want to wait until you are the only user of the Access database so that you can save your major design changes and Access can save your minor design changes.

A data access page is handled differently. Although creating, renaming, moving, and deleting a data access page still requires promotion to exclusive mode (because this requires changing information in the Access database), editing a data access page does not require promotion to exclusive mode (because the corresponding HTML file exists in the file system outside the database).

Differences between major and minor design changes

If you don't have exclusive access to an Access database, Access will alert you when you might not be able to save the following major design changes:

• Changes to database objects (except table and queries) made in Design view
• Changes to a form property sheet in Form view
• Compiling the project, modifying project properties, or adding or removing a reference in the Visual Basic Editor
• Renaming, pasting, or deleting a database object
• Saving a database object as another type of database object
• Adding or modifying controls on a command bar
• Editing custom groups in the Database window
• Creating, renaming, moving, and deleting a data access page

If you don't have exclusive access to an Access database, Access doesn't alert you when you might not be able to save the following minor design changes:

• Changes to datasheet formatting properties, such as line styles and fonts
• Freezing, unfreezing, hiding, and showing datasheet columns
• Adjusting datasheet column width and row size
• Changes to the filter or sort order for a form or datasheet
• Changes to the state of a subdatasheet (whether expanded or collapsed)
• Changes to the OLE/DDE link of an OLE object or changes to the contents of an unbound object frame
• Changes to printer settings
• Changes to a command bar, such as its location and whether or not it's visible
• Changes to the layout of the Database window

Strategies for sharing Access database design and development

If you are going to share the design of an Access database, consider using the following strategies:

• Assign specific objects or groups of objects to specific developers. For example, assign one developer to design forms and another to designs reports. Then, each developer can work on a private copy of the Access database. When your development team is ready for testing, assembly, and production, each developer can export the database objects from their private copy of the database to a main Access database.
• Use a source code control program. For example, you can use a combination of Microsoft Visual SourceSafe version 6.0 and the Source Code Control add-in that enables Visual SourceSafe Code support in Access. This add-in is available from the Microsoft Office XP Developer. If you do use Visual SourceSafe, it's a good idea to run the Performance Analyzer (on the Tools menu, point to Analyze, and then click Performance) on a regular basis to maintain optimum performance.

Tips for saving design changes in a shared database

• If another user has a table open or is viewing data in queries, forms, or reports based on the table, you can't make any changes to the table's design. Microsoft Access informs you that it is read-only.
• If objects are dependent on one another, update all of them at the same time so that other users don't open inconsistent versions of the objects. For example, if you want to add fields to a form, make sure to add the fields to the underlying query before you update the form.
• If you change a query that another user has open, that user must close and reopen the changed object to use the latest version. With Microsoft Visual Basic for Applications (VBA) modules, other users can't run the updated Function or Sub procedures until they close and reopen the Access database.