Detecting an uncompiled database and automatically recompiling

Making sure that a database is always in a compiled state is very important. If you release a database as an .accdb file, your customers may make changes to the application and then complain because it's running slowly. Although some of your customers may be serious developers, most often the users who make changes to Access databases don't understand compiling or compacting a database.

Tip: To see if your database is compiled, open the Visual Basic window for any module, display the Debug window at the bottom of the editor, and type ? IsCompiled. If the database is compiled, it displays True. If it's in a decompiled state, it displays False.

To solve this problem of changes being made by users who don't understand compiling or compacting a database, you can create an interface that automatically detects whether the database is not in a compiled state and then gives the user the option of compiling the application. This automatic detection runs each time the database is opened. The user still has to compact the database, but the form takes care of compiling the database, the part many users have trouble comprehending.

One line of code can be added anywhere in your program to detect an uncompiled application and start the process:

The form is displayed if the application isn't compiled. Users are given the choice of recompling or proceeding without compilation.

Making small changes to large databases

When you're making lots of changes to a database, you're constantly opening and closing objects. Work with a copy of the database, and then when you have the changes just the way you want, export the changed objects to the production database. (An exported object with the same name as an object in the production database is exported with a 1 at the end of its name.) You can then delete the original object in the production database, and rename the exported objects. New objects are exported with their name intact.

The fewer changes to a large database, the better off you are. By following the tips and techniques in this section, you'll have fewer problems and be more productive.