ACC2000: Database Size Is Different When Database Is Converted to Access 2000

This article explains why a Microsoft Access database may change in size when you convert the database from an earlier version to Microsoft Access 2000.

Uncompiled Code

When you convert or open a database from an earlier version of Microsoft Access in Access 2000, Microsoft Access converts your Visual Basic for Applications code. This can change the size of your database for various reasons.

To improve performance, Microsoft Access 2000 stores Visual Basic for Applications modules in a compiled state. Microsoft Access 2000 .mdb file types can contain both editable and compiled source code, which, depending upon the amount of code, can impact the size of the .mdb file. In Design view of your code, be sure to click Compile <DbName> on the Debug menu to place the code into a compiled state.

Microsoft Access 2000 also has a feature called MDE files. A .mde file is a database from which all editable source code has been removed so that only the compiled code remains. This results in a smaller database size.

Non-Compacted Databases

The modification of database objects, such as modifying queries, forms, reports, macros, and so on can cause a database to increase in size. The same behavior occurs when you add, edit, or delete records in your tables. To decrease the size of your database, you can run the Compact Database command. To do so, on the Tools menu, point to Database Utilities, and then click Compact and Repair Database. A database that has not been compacted before it is converted to Access 2000 will be compacted as part of the conversion process. This decreases the size of the database as it is converted to Access 2000.

Unicode Feature

New to Microsoft Access 2000 is the Unicode character-encoding scheme. This scheme is used to represent data in a Text, Memo, or Hyperlink field. In Unicode, each character is represented by two bytes instead of a single byte.

Unicode can support a maximum of 65,536 characters. However, Unicode represents each character as two bytes, instead of one byte. As a result, the data in a Text, Memo, or Hyperlink field requires more storage space than in earlier versions of Access.

You can offset the effect of Unicode character representation and ensure optimal performance by setting the Unicode Compression property of the Text, Memo, or Hyperlink field to Yes. This is the default when you create these new fields in an Access 2000 table. When you convert a database from an earlier version of Microsoft Access to Access 2000, the Unicode Compression property of any Text, Memo, or Hyperlink field is automatically set to Yes.

Data in a Memo field is not compressed unless it requires 4,096 bytes or less of storage space after compression. As a result, the contents of a Memo field may be compressed in one record, but may not be compressed in another record.

For additional information about what can cause an increase in database size, click the article numbers below to view the articles in the Microsoft Knowledge Base: For additional information about lightweight objects in Access 2000, click the article number below to view the article in the Microsoft Knowledge Base: For more information about converting a database, click Microsoft Access Help on the Help menu, type converting databases in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the Compact Database command, click Microsoft Access Help on the Help menu, type compacting databases in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about how to use MDE files in Microsoft Access 2000, click Microsoft Access Help on the Help menu, type MDE files in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about Unicode compression, click Microsoft Access Help on the Help menu, type unicode support in Microsoft Office in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.