Part IV: Professional Database Development
874
Recognizing that compiling and
compacting may not be enough
As you add, delete, and modify objects, Access doesn’t always clean up after itself. You’ve probably
learned that, after you make changes to your objects, especially VBA code, you should open any
module and choose Debug ➪ Compile Database Name, save the module, and close the VBA editor
window. After you do this, click the Microsoft Office button, select the Info tab, and click the
Compact and Repair This Database button. This action compacts the database with the same name
and reopens the database.Tip
If you prefer a less aggressive approach, close the database first and compact the database to a different name,
effectively creating a compacted backup. You can then start working with the new database, or delete the old
one and rename the new database to the original name.
Compiling and compacting may not be enough to solve some of the problems mentioned in the
preceding section. Databases have been known to grow in size after compiling and compacting —
even without adding new objects, code, or data. Sometimes, strange things happen to databases
without a good explanation. The database might not compile code properly if the database is too
large, or you might see compile errors on perfectly written code. The database might run slowly
even if there’s nothing wrong. There are a more few techniques to use, even when you think you’re
out of options.Rebooting to get a clean memory map
Strange behavior in any program often gets better when you reboot your computer. Access applica-
tions are particularly prone to memory leaks (situations that arise when the application is unable to
release memory it’s acquired so that the operating system can use it for other purposes), especially
if you’re going in and out of form, report, and module design and using a lot of data objects
(mostly recordsets).If you don’t want to reboot, at least close your database and exit Access as a first step in resolving a
problem.Fixing a corrupt form by removing the record source
Sometimes, you may have a form that doesn’t run properly. Try opening the form in Design view
and removing its record source. Then close and save the form, reopen it in Design view, and
restore the record source. When the record source of an Access form or report is changed, it forces
various pieces of internal code behind the form to be rebuilt and may help resolve the issue.Creating a new database and importing all objects
Having your database as clean as possible is important. Although I’m not sure if gremlins crawl
into some obscure portion of the database file, I am sure that you can’t import or export resident
gremlins. A technique that often proves successful is to create a new database and import all the
objects from the original database. Access makes it easy to import all of a database’s objects: