MS-Access / Getting Started

---

Recognizing that compiling and compacting

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 applications 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. The crawl into some obscure portion of the database file, 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:

1. Open a new empty database.
2. Select the External Data ribbon tab.
3. Click the Access option on the Import group.
4. Select the database you're having problems with.
5. Ensure that the Import Tables, Queries, Forms, Reports, Macros, and Modules into the Current Database option is selected.
6. Click OK.
7. Click on Select All for each of the relevant tabs.
8. Click OK.

If you have any custom menus and toolbars, import/export specifications, or Navigation Pane groups, remember to select the appropriate items in the Import Objects dialog box. If the old database contains custom database properties, you have to create them again because they can't be imported.

Caution: If you use externally referenced libraries or add-ins, you must manually reference these libraries in the new database. Choose Tools → References in the VBA editor to do this. As well, you'll need to reset any of options specified on the Current Database tab, such as Application Title, Display Form, and so on (choose File → Access Options).