Saving objects in a large Access 2003 database may be slower than in earlier versions of Access

When you try to save design changes that are made to a Microsoft Office Access 2003 object and that object contains a module, such as a form, a report, or a standard module, Access 2003 may take more time to save the object than in earlier versions of Microsoft Access, such as Microsoft Access 95 or Microsoft Access 97. This problem occurs when the Access 2003 database contains large Microsoft Visual Basic for Applications (VBA) projects.

Microsoft Visual Basic Environment (VBE) that is hosted in Access 2003 dictates how Access 2003 stores the VBA project in a database. A VBA project is the set of standard modules and of class modules (including form modules and report modules), library references, and other properties that are stored in an Access 2003 database file (.mdb) or in an Access 2003 project file (.adp).

VBE requires that all host applications read and write the whole VBA project at the same time. Since the integration of VBE in Access 2000, the Access save model has followed this requirement. When you save a database object, the whole VBA project is saved instead of just the individually-modified modules. This means that if your database has a large VBA project, the database takes longer to save because the whole project is rewritten instead of just the individual objects that you modified. This model differs from the save model that is used in Access 95 and in Access 97. In Access 95 and in Access 97, the VBA project items are stored as individual rows in system tables. At save time, Access saves each modified object individually and then merges those changes back to the VBA project of the database.

When you save a VBA project, there are two representations of the VBA project that are saved. There is a canonical (text) representation, and there is a compiled representation. The canonical portion of the VBA project represents the textual code that you see in a module in VBE. The compiled portion of the VBA project is the binary storage of the code that is created when you compile the project and then you save the project. For example, in Access 2003 .mdb files and in Access 2003 .adp files, both portions of the project are stored in the file. In earlier versions of Access .mde files and of Access .ade files, the canonical portion of the project is removed and only the compiled portion is stored in the file.

There are improvements in the Access 2003 save model when you compare the Access 2003 save model to the Access 2000 save model. In Access 2000, if you edit a module and then you save the module, both the canonical portion of the project and the compiled portion of the project are written to the file. This is true even if you do not compile the project. In Access 2003, if you do not compile the project, only the canonical portion is written to the file when you save the file. If you compile the Access 2003 project and then you save the Access 2003 project, both the canonical portion of the project and the compiled portion of the project are saved to the file. This results in potentially shorter save times in Access 2003 than in Access 2000.

As an example, assume the size of the VBA project in an Access file is 10 megabytes (MB) and that you make a one-line code change to one module that is 100 kilobytes (KB). Access 95 and Access 97 only have to write back that one module during the save. This is the 100 KB module. In Access 2000, the same operation forces the whole 10 MB VBA project to be rewritten back to the file. In Access 2003, the same operation writes only the one 100 KB module back to the file (unless you compile the project first and then that forces the whole project to be rewritten).

In addition to the consistency that is provided with other VBE host applications, the Access 2003 project-storage model has the following advantages:

• The project-storage model eliminates dependency on the Microsoft Jet database engine.
• The project-storage model improves stability of Access-specific objects.

Eliminate dependency on the Jet database engine

In Access 2003, you can create both Access project files (.adp) and Jet database files (.mdb). If you use an Access 2003 project file, you can use Microsoft SQL Server as an alternative database engine to the Jet database engine. Before Access 2000, all Access-specific objects (forms, reports, macros, modules, and command bars) depend on the Jet database engine for storage. These objects are stored in Access-specific system tables in the Jet database. Because Access 2003 can use SQL Server as an alternative to Jet, Microsoft developed a storage mechanism for Access 2003-specific objects that does not rely on Jet.

Improve stability of the Access 2003-specific objects

The project-storage model in Access 2003 improves stability for both the Access 2003-specific objects and for Visual Basic projects. VBA has never permitted multi-user editing of Visual Basic projects without source code control.

In Access 95 and in Access 97, you can circumvent this restriction by hiding project changes from VBA that are made in a multi-user environment. You can then merge the project changes into the project later. However, this method sometimes has a negative impact on the stability of the Visual Basic project. When you modify the design of an Access 2003-specific object, Access 2003 requires an exclusive lock to make sure that the project or that the database has only one person who is trying to make a change to that database.