Microsoft Access Questions

What techniques can be used to minimize the size of a Microsoft Access database? Here are some obvious possibilities. Which of them actually help?

Use the compact and repair feature.
Normalize tables, if possible.
Delete unneeded rows (seems to have no effect on file size until the database is compacted)
Delete unneeded tables (seems to have no effect on file size until the database is compacted)
Choose smaller field sizes.
- Choose actual field types instead of Variants
  - Please clarify. Is this for code or file size?
  - It's mainly for gaining compile-time type checking and thereby improving static TypeSafety in VBA code. I'm not sure it will help minimize the size of an Access database, except perhaps insignificantly, because tables do not support Variant column types. As such, the point probably doesn't belong here.
- Choose numeric types with fewer bits. For example, Integers instead of Double or Long.
- Use AsciiString?s instead of UnicodeString?s.
  - According to Access 2003's help item for "About compressing the data in a Text, Memo, or Hyperlink field (MDB)", Access 2003 uses Unicode for Text, Memo, and Hyperlink fields. Each such field has a Unicode Compression property. Setting this property to yes eliminates the storage space for leading zeroes in Unicode characters. If a Memo cell cannot be compressed to 4,096 bytes or less, Access will not compress it.
- Use strings instead of Memo fields
- Use shorter text fields. For example, 4 characters instead of the default 50 characters, or 50 characters instead of the maximum 255 characters.
  - This is unlikely to have any effect. According to Access 2003's help topic "About differences between data types in an Access database and Access project", "Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored."
Remove infrequently-used indexes. Default Access will often automatically create indexes for new tables without asking. There may be a few lurking around that you don't need.

The third law of optimization: Always use a profiler.

A real life example

An Access 97 database was migrated to Access 2003. The database has two huge tables, 1 small "Paste errors" table, 5 small "Import errors" tables, and other tables. Each measurement of file size was done by making the relevant change(s) in Access 2003, then compacting the database in Access 2003, then reading the file size in Windows Explorer.

One of the two huge tables has 600,000 rows, each with 106 bytes of data plus an empty 255 character string. The other huge table has 530,000 rows, each with 94 bytes of data plus an empty 255 character string. One of the "other tables" has 500,000 rows, each with 80 bytes of data. The remaining tables are much smaller.

If Access did not do any internal optimization, the compacted file size would be about 600,000 * 361 bytes + 530,000 * 349 bytes + 50 MB in other tables = 450 MB. The actual compacted file size is about 280 MB, so Access clearly does some internal optimization. As noted above, Access 2003 does not store "space characters for unused positions in" text and memo fields.

If Access did not do any internal optimization, then eliminating the empty 255 character string fields would reduce the compacted file size to about 600,000 * 106 bytes + 530,000 * 94 bytes + 50 MB in other tables = 165 MB. The actual compacted file size is about 520 MB, so Access clearly does some bloating.

What can be done to reduce the file size of this Access 2003 database?

Discussion