ACC2000: Saving Objects in Large Database Slower Than in Earlier Versions

ID: Q246306

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

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

CAUSE

This behavior is caused by changes in the project storage model in Microsoft Access 2000 required by the Visual Basic Environment.

MORE INFORMATION

The integration of the Visual Basic Environment in Microsoft Access 2000 required Microsoft Access to change its VBA project storage architecture. The Visual Basic Environment requires that host applications, such as Microsoft Access, Microsoft Word, and Microsoft Excel, read and write the entire VBA project at once. A VBA project is the set of standard and class modules, including form and report modules, stored in a Microsoft Access database (MDB) or project (ADP).

In Microsoft Access 97, VBA project items were stored as individual rows within the MSysModules2 system table. At save time, Microsoft Access would save each "dirty" object individually, and then merge those changes back into the database's VBA project.

In Microsoft Access 2000, the VBA project is stored as a binary large object (BLOB) within the MSysAccessObjects table. 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.

For example, assume the size of the VBA project within your database is 10 megabytes (MB), but you are modifying a form's module that is 100 kilobytes (KB). In Microsoft Access 97, only the form module would be written at save time, meaning that Microsoft Access only had to write 100 KB to the database. In Microsoft Access 2000, the same operation would force the entire 10 MB VBA project to be rewritten to the database.

In addition to the consistency provided with other Visual Basic Environment host applications, the new project storage architecture 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 2000, you can now 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. In the past, 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 2000 can 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 Microsoft Jet.

Improves Stability of Access-Specific Objects

The new project storage model improves 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 were able to circumvent this restriction by hiding project changes made in a multiuser environment from Visual Basic for Applications, and merging them into the project at a later time. However, this impacted the stability of the Visual Basic project. When a user modifies the design of an Access-specific object, Access 2000 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 2000, click the article number below to view the article in the Microsoft Knowledge Base:

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

Additional query words: prb takes more time longer slow

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