Developer Considerations for Choosing File Formats in Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Find out when you should use the new .accdb file format in Microsoft Office Access 2007 and when you should use the earlier .mdb file format. (4 printed pages)

Mike Stowe, Microsoft Corporation

October 2007

Applies to: Microsoft Office Access 2007

Contents

• Overview of File Format Support in Access 2007

• When You Should Use the ACCDB File Format

• Converting MDB Files to ACCDB Format

• When You Should Not Use the ACCDB File Format

• File Types Supported by the Access 2007 File Format

• Conclusion

• Additional Resources

Overview of File Format Support in Access 2007

Microsoft Office Access 2007 introduces a new default file format (.accdb) that supports a number of product enhancements. This article explains when you should use the new file format, and when you might need to use the earlier version (.mdb) file format.

When You Should Use the ACCDB File Format

When you want to use Access 2007-specific features and ensure that only Access 2007 users can open your files, you should use the .accdb file format. For example, in a corporate environment with only Access 2007 installed on all the desktop computers, you would want to develop your applications using the .accdb file format.

The new features that are supported in only the .accdb file format include:

• Multivalued fields.  Most database programs, including earlier versions of Access, allow you to store only a single value in each field. However, in Access 2007, you can create a lookup field that allows you to store more than one value in each field. In effect, Access creates a many-to-many relationship within the field and hides the details of the implementation by using system tables.

• Attachment data type.  Use the new Attachment data type to easily store all types of documents and binary files in your database without unnecessary database bloat. To maximize space usage, attachments are automatically compressed when it is appropriate. For example, you can attach a Microsoft Office Word 2007 document to a record or save a series of digital pictures. You can even have multiple attachments to a single record.

• Integration with Windows SharePoint Services 3.0 and Outlook 2007.  In earlier versions, Access files are blocked from Windows SharePoint Services and Microsoft Office Outlook 2007 because unsafe code could be included in an Access database. Access 2007 implements a new format that allows code to be either verified as safe or disabled. This feature makes it possible to integrate Access databases much more fully with Windows SharePoint Services and Office Outlook 2007.

• History tracking for Memo fields.  Memo fields are useful for storing large amounts of information. In Access 2007, set the AppendOnly property to True so that Access keeps a history of all changes to a Memo field. You can then view a history of those changes. This feature also supports the versioning feature in Windows SharePoint Services 3.0 so that you can use Access to track changes in a "multiple lines of text" field that is stored in a SharePoint list. To do this, set the Append Changes to Existing Text option on the Memo field to Yes.

• Encryption improvements.  You can choose to set a database password and encrypt the contents of your database. When you do this by using Access 2007 and a file that uses the new file format, Access uses CryptoAPI to encrypt the data.

Converting MDB Files to ACCDB Format

You can convert a database that was created in Microsoft Office Access 2003, Access 2002, Access 2000, or Access 97 to the new .accdb file format.

Converting Access 2000 or Access 2002–2003 Databases

To convert an Access 2000, 2002, or 2003 database (.mdb files) to the .accdb file format, you must first open the database in Access 2007, and then save it in the .accdb file format. To do this, follow these steps.

To convert an Access 2000 or Access 2002–2003 database

1. In Access 2007, open the database that you want to convert to the .accdb file format.

2. Click the Microsoft Office Button, point to Save As, and under Save the database in another format, click the file format to which you want to convert.

   If any database objects are open when you use the Save Database As command, Access prompts you to close them before you create the copy. Click Yes to make Access close the objects, or click No to cancel the entire process. Access also prompts you to save any changes.

3. In the Save As dialog box, type a file name in the File name box, and then click Save.

   Access creates the copy of the database, and then opens the copy. Access automatically closes the original database.

Note
You cannot convert a replicated database to the .accdb file format. However, you can use Access 2007 to create a replica of a database that is formatted in either the Access 2000 or the Access 2002–2003 file format.

Converting Access 97 Databases

To convert an Access 97 database (.mdb file) to the .accdb file format, you must first open the database in Access 2007 and then save it in the .accdb file format. To do this, follow these steps.

To convert an Access 97 database

1. In Access 2007, open the database that you want to convert to the .accdb file format.

2. In the Database Enhancement dialog box, click Yes.

   Access creates the copy of the database, and then opens the copy. Access automatically closes the original database.

When You Should Not Use the ACCDB File Format

Access 2007 can create databases in the Access 2002–2003 (.mdb) and Access 2000 (.mdb) file formats. You should continue to use the .mdb file format if people who are using earlier versions of Access need to use your databases. You cannot use earlier versions of Access to open databases created in .accdb format. Also, earlier versions of Access cannot link to tables stored in databases created in .accdb format.

Databases that are created in the .accdb file format do not support replication. However, you can use Access 2007 to replicate a database that was created in the .mdb file format.

Databases that are created in the .accdb file format do not support user-level security. However, if you open a database from an earlier version of Access that has user-level security applied, those security settings remain in place when you open that file in Access 2007. In addition, you can start the security tools provided by Access 2003—the User-Level Security Wizard and the various user and group permission dialog boxes—from Access 2007.

Note
For more information about securing your databases in Access 2007, see Security Considerations and Guidance for Access 2007.

Databases that were created in Access 2000, Access 2002, and Access 2003 should function as expected when you use them in Access 2007. However, using features that are new to Access 2007 could cause unexpected results when you share the database with users of earlier versions. For more information about using Access 2007 in mixed environments, see Migration Considerations for Access 2007.

Changing the Default File Format

If you have upgraded to Access 2007, but you share files with others who have not, you may want to change the default file format that is used when you create new databases. To do this, follow these steps.

To change the default file format in Access 2007

1. Click the Microsoft Office Button, and then click Access Options.

2. In the Access Options dialog box, click Popular.

3. Under Creating databases, in the Default file format box, select the file format you want as the default, and then click OK.

File Types Supported by the Access 2007 File Format

Although .accdb is the most common type of database that you create in Access 2007, the Access 2007 file format supports several file types. Table 1 summarizes the file types used by the Access 2007 file format.

Table 1. File types supported by the Access 2007 file format

Conclusion

Access 2007 introduces the new .accdb file format, which supports several improvements in the product, including multivalued fields, the new Attachment data type, better integration with Windows SharePoint Services and Office Outlook 2007, history tracking in Memo fields, and encryption improvements. This article describes when you should use the new .accdb file format and when you should use the earlier .mdb file format.

Additional Resources

For more information about Access 2007 and Access file formats, see the following resources:

• Transitioning Your Existing Access Applications to Access 2007

• Security Considerations and Guidance for Access 2007

• Migration Considerations for Access 2007

• Access Developer Portal

• Access 2007 Resource Center