Working with Large Access Databases

When someone mentions large databases in Microsoft Access, he's generally thinking about a database containing tables holding hundreds of thousands of records. Although this is a large database, another definition is a database containing hundreds or thousands of objects - tables, queries, forms, reports, and lots of VBA modules. Although you can sometimes solve data performance problems by using SQL Server as the database engine, you'll probably have a much more complex problem dealing with applications containing many queries, forms, reports, and modules.

If your database has hundreds of objects, especially forms and reports, you may have run into problems that cause your database to exhibit strange behavior, including

• Not staying compiled
• Growing and growing, even after compiling and compacting
• Running more slowly over time
• Displaying the wrong record in linked subforms
• Displaying compile errors when you know that the code is correct
• Frequent database corruption

Compacting your database doesn't always work as advertised. Compiling and saving all modules takes a long time. After you compact and open the database, the database is uncompiled again. If you work with large databases, chances are good that you've had these experiences. This section shows how to solve these problems and get your databases up and running fast again.

Understanding how databases grow in size

Many things can cause a database to grow. Each time that you add an object to an Access database (.accdb) file, it gets larger. And why shouldn't it? You're certainly using more space to define the properties and methods of the object. Reports and forms take up a lot of space because of the properties associated with forms and reports and their controls. Table attachments (links) and queries take up very little space, but VBA code grows proportionally with the number of forms and reports. Storing data in a program database (rather than in a linked back-end database) also takes up space.

Many other things cause a database to grow: Each time you add another new form or report, more space is used. Each time you add a new control and define some properties, even more space is used. When you define any event in a form or report that contains even a single line of VBA code, more overhead is used, because the form or report is no longer a lightweight object. This requires more space and resources than a form or report containing no VBA code. Embedded images in forms and reports also use space. Embedded OLE data, such as pictures or sound, use more space than unbound objects or images.

Every time you make a change to any object - even a simple one - a duplicate copy of the object remains in the database file until you compact the database. Within a few hours of work, Access databases can begin to grow larger and larger. If the database contains thousands of lines of VBA code, the database can grow to two or three times its original size very quickly, especially when it's compiled and before it's compacted.