ACC2002: Saving Objects in a Large Database Is Slower Than in Earlier Versions (287603)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287603
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 246306.

SYMPTOMS

When you try to save design changes that you made to a Microsoft Access object that contains a module (a form, a report, or a standard module), it may take longer to save the object than it did in Microsoft Access 95 or 97. This is especially true in databases that contain large Visual Basic for Application (VBA) projects.

MORE INFORMATION

The Visual Basic Environment (VBE) hosted within Microsoft Access dictates how Access stores the VBA project within a database. A VBA project is the set of standard and class modules (including form and report modules), library references, and other properties stored in a Microsoft Access database (MDB) or project (ADP) file.

The VBE requires that all host applications read and write the entire VBA project at once. Since the integration of VBE in Access 2000, the Access save model has adhered to this requirement. At save time, the entire VBA project is saved, rather than just the individual dirty modules. This means that if your database has a large VBA project, it will take longer to save because the entire project is rewritten, rather than just the individual objects that you modified. This model differs from the save model used in Microsoft Access 95 and 97. In those versions, VBA project items were stored as individual rows within system tables, and at save time Access would save each dirty object individually, and then merge those changes back into the database's VBA project.

When a VBA project is saved, there are two representations of it. There is a canonical, or text representation, and a compiled representation. The canonical portion of the VBA project represents the textual code that you see in a module within the VBE. The compiled portion of the project is the binary storage of that code when the user compiles and saves the project. For example, in Access MDB and ADP files, both portions of the project are stored within the file, whereas in MDE and ADE files, the canonical portion of the project is removed and only the compiled portion is stored within the file.

There have been improvements in the Microsoft Access 2002 save model over the Access 2000 save model. In Access 2000, if you edited a module and saved it, both the canonical and compiled portions of the project were written to the file, even if you didn't compile the project. In Access 2002, if you do not compile the project, only the canonical portion is written back when saving, which results in shorter save times than in Access 2000. If you compile the project and save it, both the canonical and the compiled portions of the project are saved to the file.

For example, assume the size of the VBA project within an Access file is 10 megabytes (MB), and you make a one line code change to one module that is 100 kilobytes (KB) in size. Microsoft Access 95 and 97 would only have to write back that one module during the save, meaning it would have to write 100 KB of data back to the file. In Microsoft Access 2000, the same operation would force the entire 10 MB VBA project to be rewritten to the file. In Microsoft Access 2002, the same operation would result in only the one module being written back to the file, unless you compiled the project first, which would force the entire project to be rewritten.

In addition to the consistency provided with other Visual Basic Environment host applications, this project-storage model has other advantages as well:
  • It eliminates dependency on the Microsoft Jet database engine.
  • It improves stability of Access-specific objects.

Eliminates Dependency on the Microsoft Jet Database Engine

In Microsoft Access 2002, you can create Microsoft Access project files (.adp) as well as Microsoft Jet database files (.mdb). By using an Access project file, you can use Microsoft SQL Server as an alternative database engine to Microsoft Jet. Before Access 2000, all Access-specific objects (forms, reports, macros, modules, and commandbars) were dependent upon the Microsoft Jet database engine for storage. These objects were stored in Access-specific system tables within the Microsoft Jet database. Because Microsoft Access 2002 can use Microsoft SQL Server as an alternative to Microsoft Jet, Microsoft developed a storage mechanism for Access-specific objects that does not rely on Microsoft Jet.

Improves Stability of Access-Specific Objects

The project storage model in Access 2002 improves 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 sometimes had a negative impact on the stability of the Visual Basic project. When a user modifies the design of an Access-specific object, Access 2002 requires an exclusive lock in order to ensure that the project has only one editor.

REFERENCES

For additional information about project storage changes in Microsoft Access 2002, click the article number below to view the article in the Microsoft Knowledge Base:

283228 ACC2002: Exclusive Lock Required for Saving Design Changes to Access Objects

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB287603
©2003 Microsoft Corporation. All rights reserved.