ACC2000: Saving Objects in Large Database Slower Than in Earlier Versions (246306)

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.