Understanding the .mdb File Upgrade Process

Access 2003 is the first Access release that doesn't introduce a new Jet database format. Prior to Access 2003, each version of Access 1.0, 1.1, 2.0, 95, 97, 2000, and 2002 had a different database file structure at the binary (byte) level. The differences between 16-bit .mdb files created with versions 1.0 and 1.1 were relatively minor; thus you could use the Compact feature of Access to convert version 1.0 .mdbs to version 1.1, or vice versa. The file formats of later versions of Jet databases are sufficiently different to require, with the exception of the Access 2000 and 2002 formats, one-way conversion during the upgrade process.

Tip

Access 2003 uses the 2000 and 2002 version of Jet 4.0 files interchangeably. Only very large Access front-end applications benefit by upgrading from the default Access 2000 (version 9.0) .mdb structure and the 2002 version (10.0). Version 10.0 supports a few VBA-related properties and methods that aren't available in version 9.0.

Unless you encounter slow compilation of VBA code in the Access 2000 version or need to use version 10.0's VBA extensions, there's no reason to change the .mdb format to 10.0. The term Access 2003 in this chapter means the application version, not the .mdb or .adp file format. Access 2002+ refers to features that remain the same in Access 2002 and 2003. Jet 4.0 means either Access 2000 or 2002 file format.

Access 2002 lets you convert data .mdbs from Jet 4.0 to Access 97 (Jet 3.5x) format, which makes them accessible by Access 97 front-end applications. You also can down-convert Jet 4.0 application .mdbs that don't contain objects such as Data Access Pages (DAP) or references to objects ActiveX Data Objects (ADO and ADOX) or Data Access Objects (DAO) 3.6 that are missing in Access 97. After you downgrade a front end to Jet 3.51, you must make all design changes in Access 97. Downgrading a complex Jet 4.0 front end to Access 97's Jet 3.51 isn't a piece of cake, especially if the application uses libraries. Expect to spend a few hours (or days) fixing references and tracking code compilation errors.

Caution

Don't upgrade shared data (back-end) .mdb files or workgroup .mdw files to Jet 4.0 format until all users who connect to these files have upgraded their application .mdb (front-end) files to Jet 4.0. Prior versions of Access can't link to Jet 4.0 tables; you receive an "Unrecognized data format" error message if you attempt to link an Access 9x front end to Jet 4.0 .mdb or .mdw files.

Access 2002 and 2003 save ordinary forms, reports, and modules in compound document files (DocFiles), which are stored within application .mdb files. This change supports Access Data Projects (ADP), which save application objects in .adp DocFiles, not in conventional .mdb files. The "Upgrading from MSDE 1.0 to the SQL Server 2000 Desktop Engine" section later in this chapter covers issues you encounter when moving to the new MSDE 2000 version.