Chapter 24: Optimizing Access Applications
873
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
l (^) Not staying compiled
l Growing and growing, even after compiling and compacting
l (^) Running more slowly over time
l Displaying the wrong record in linked subforms
l (^) Displaying compile errors when you know that the code is correct
l 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 que-
ries 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.