Compact and Repair

The reasons for the need to compact and repair Access databases can be found in the help files under Compact and Repair. However, many people don't know about it or don't realize its importance. A newly created database can easily become over a megabyte in size even without data. It is very important to run compact and repair as often as possible. Depending on the number of transactions, you will ideally run compact and repair once per day; all users must first exit the database. For single-user databases, Access 2000 has an option under Tools-Options, General tab to compact on exit. Use it! Compacting the database is especially helpful prior to emailing it.

Access Objects

There are seven types of objects in an Access database. Here are explanations in simple terms to help you to understand the purpose of the objects.

Tables

Tables store the data. These are the most important objects in your database. If these are not designed properly, then there is no point in continuing to design other objects. Access tables are very much like Excel worksheets. In Excel, however, you'll have difficulty storing a client's name only once, and having many invoices for that client. Instead, every client's record would need to contain the clients name, address, etc. In Access, you'd have one client record and many invoice records that are related to that client. The Invoices table would contain a field that is linked (related) to the Client table.

Queries

Queries ask questions of your data. When you run a query, Access provides you with a temporary view of the data that you have requested. You can build many queries without increasing the size of your database because only the criteria is saved, not the data the data that is produced when the query is run. In our example, you may want to retrieve the client's name and address from the Client table, and the sum of that client's invoices.

Forms

Forms are yet another way to view your data, usually one record at a time. They are also created to allow an organized manner of entering new data, and changing or deleting existing data. Many people confuse forms with reports, because a form used to be something we printed. You can create subforms too. Again, in our example, we could have a form with the client's information and a subform below it with a list of that client's invoices. An invoices form may have a subform that shows details of the items purchased under that invoice.  The Form Wizard is a great tool for creating forms. If you don't like what is produced, you can always change it. It's very helpful in learning to understand the design view of a form.

Reports

Reports are pretty much anything you want to print from your database, whether it is a single record or an entire summary. You may want to print individual invoices, end-of-month statements for your clients, or summary sales reports. The Report Wizard is a great tool for creating reports, which includes labels. If you don't like what is produced, you can always change it. It's very helpful in learning to understand the design view of a form. Many of the built-in form designs contain a lot of graphic elements, such as lines, that can be removed.

Pages

These are Data Access Pages. You need them only if you're putting your database on the web or an intranet. They are very similar to Forms.

Macro

Access has many built-in macros. If you believe that you need to know VBA for the simplest macro, then you may want to check out all of the built-in macros first. Practically all you need to do is pick a macro, tell it what you're using it on, and sometimes give a file name or other information.

Good Table Design

Tables are the most important objects in Access. They are the foundation of your database, no matter how we might try to get around it. Until you have your tables perfectly designed, do not even bother creating any other objects. Bad table design is why a lot of people quit trying to use Access. They design the table badly, and then expect queries to work or reports to provide proper summaries.

Normalization

We've worked with many people who have completely operational databases and now need help because they can't manage to get the information they want. They have fancy buttons on their forms, and their reports are beautiful. But because they never normalized their data, they may never be able to extract the information they need without redesigning their tables and, hence, a lot of these fancy items that were based on the poor table design.

Microsoft explains it best in their free download and webcast. Both are available at this Technet Article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534