Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Frank C. Rice
Microsoft Corporation
July 2001
Applies To:
Microsoft® Access 2002
Summary: This article explains the reasons for the two file formats in Microsoft Access 2002, and describes the issues that developers should consider when choosing one file format over the other. (5 printed pages)
Download ODC_AcFormat.exe.
Contents
Introduction
Why the New Choice?
Sharing Files Between Access 2000 and Access 2002
Differences in Macros and the RecordSource Property
Access 2002 Code Will Run Slower in Access 2000
MDE and ADE Files Must Be Created with Access 2002
Programmability Differences in Access 2002
Access 97 Databases Must Still Be Converted
Sample Database to Assist You in Finding Access Files
Conclusion
Introduction
Unlike the previous versions of Microsoft® Access, users who upgrade to Access 2002 don't need to convert files created with Access 2000 to a new format in order to continue using them. Files in Access 2000 file format—both databases (.mdb) and Access projects (.adp)—can now be used directly in an Access 2002 application by utilizing the default Access 2000 file format. Conversely, files developed with Access 2002 using the default Access 2000 file format can be used in Access 2000 without conversion. This makes developing and deploying applications in a mixed Access 2000 and Access 2002 environment much simpler than in previous versions of Access.
However, there are limitations you should be aware of when using Access file formats earlier than Access 2002 and when developing in this sort of mixed environment. This article describes the rationale behind the two file formats and the issues associated with developing and using files in each of these formats.
Why the New Choice?
When you want to use Access 2002-specific features and ensure that only Access 2002 users can open your files, you should use the Access 2002 file format. For example, in a corporate environment with only Access 2002 installed on all of the desktop machines, you would want to make sure that you developed your applications using the Access 2002 file format.
The primary goal for making Access 2000 the default file format in Access 2002 was to provide Access 2000 users with the ability to upgrade and continue using their existing databases and Access projects without interruptions or conversions. In an environment with both Access 2000 and Access 2002 installations, the default Access 2000 file format ensures that your users will still be able to share files. The following table shows the compatibility between file formats from a particular version of Access and other versions of Access. "Yes" in this case means that the file can be used in the application without conversion.
| File format | Compatible with Access 97? | Compatible with Access 2000? | Compatible with Access 2002? |
|---|---|---|---|
| Access 97 format (created with Access 97) | Yes | No | No |
| Access 2000 file format (created with Access 2000) | No | Yes | Yes |
| Access 2000 file format (created with Access 2002) | No | Yes (Access 2000 features only) | Yes |
| Access 2002 file format (created with Access 2002) | No | No | Yes |
Note While using Access 2002, the features available to files in Access 2000 file format are exactly the same as those available to Access 2002 files. The Access 2002-specific features are unavailable when any file in Access 2000 file format is opened in Access 2000.
Sharing Files between Access 2000 and Access 2002
Developers deploying applications in a mixed Access 2000 and Access 2002 environment will notice several improvements for their users such as PivotTable® and PivotChart® views and XML support. Users who move their files from Access 2000 to Access 2002 will enjoy all of the new features and added functionality. Things will work as they always have without the need for conversion.
Access 2002 users can also share their files with Access 2000 users without having to worry that opening the files in Access 2000 will lose any of the Access 2002-specific properties. Access 2000 users will be able to open and use the file but with some reduced functionality. For example, in Access 2002, you can include a PivotTable view in a form or report to assist in analyzing data. When that same file is opened in Access 2000, the data will be displayed as a datasheet, allowing the user to continue working with the data. When the file is then reopened in Access 2002, it will show up again as a PivotTable view, along with any data added by the Access 2000 user.
Differences in Macros and the RecordSource Property
Access 2000 developers who modify macros or other objects will see some changes when working with files created with Access 2002. For example, macros that contain Access 2002-specific actions will have those actions commented out when the files are used in Access 2000.
Additionally, in Access 2002, the capacity of the RecordSource property was increased to handle SQL statements up to 32,000 characters in length. Files in Access 2000 file format that were created with Access 2002 and are then used in Access 2000 can execute these longer statements but can't save them. If you modify the RecordSource property of a file in Access 2000 file format while the file is in Access 2000 and then attempt to save the object, you will receive an error message saying that the RecordSource property is too long. You will therefore need to modify the RecordSource string to fit the old size of 2,000 characters or use the file only with Access 2002.
Access 2002 Code Will Run Slower in Access 2000
Microsoft Visual Basic® for Applications (VBA) code in files that were created with Access 2002 using the default Access 2000 file format will run slower in Access 2000 than VBA code in Access 2000-created files. If a developer uses Access 2002 to compile the code in a file in Access 2000 file format and then opens the file in Access 2000, the code will become de-compiled. This means that Access 2000 will need to recompile such code before running it. This recompilation will make the Access 2000 VBA code run slower than if the same file were created and used in Access 2002. It is therefore recommended that developers compile and compact their files in Access 2000 before deploying those files to a mixed environment.
MDE and ADE Files Must Be Created with Access 2002
Converting a database (.mdb) or Access project (.adp) to an MDE or ADE file, respectively, protects the intellectual property of developers by removing the source code from VBA functions and subroutines. This leaves only the compiled code which can't be viewed in a readable format; thus, the code is safe from someone wanting to copy it. However, the compiled code remaining in an MDE or ADE file must still make calls to specific properties and methods in the Access object libraries. Since new properties and methods have been added to the current version of Access 2002, any calls to these properties or methods from a file being used in Access 2000 would cause errors. To prevent this from happening, Access 2002 can only create MDE and ADE files from Access 2002 files.
Here is a summary of MDE and ADE file compatibility:
- MDE and ADE files created with Access 2000 can be opened in Access 2002 without any problems.
- MDE and ADE files created with Access 2002 cannot be opened in Access 2000.
- Additionally, MDE and ADE files can only be created with Access 2002 when using the Access 2002 file format, not the default Access 2000 file format.
This protects both the developer and the end-user from inadvertently opening a potentially incompatible file in Access 2000 and ensures that all features will work as expected.
Programmability Differences in Access 2002
There are several new features and options in Access 2002, all of which are accessible by files in Access 2000 file format when they are used in Access 2002. To ensure that files created with Access 2002 in Access 2000 file format continue to be compatible when used in Access 2000, references to the default data access object model (ADO 2.1) and the Structured Query Language (SQL) syntax (ANSI SQL 89) for Jet database engine (.mdb) files are retained.
To implement the new features and options available in Access 2002, the base compatibility level of Access 2002 files was changed to include a new default data access model (ADO 2.5), a new SQL syntax (ANSI SQL 92), and a reference to Microsoft Office XP Web Components.
The following are just some of the new or existing objects, collections, methods, and properties that were added or changed in Access 2002:
- Printers collection
- Printer object
- PivotTable property
- ChartSpace property
- AllFunctions property
- CompactRepair method
- ConvertAccessProject method
- OpenCurrentDatabase method
- RecordSource property (accepts 32,000 characters)
For more information on the programmability features added or changed in Access 2002, see "What's New for Microsoft Access 2002 Developers" in Access Visual Basic online Help.
When new PivotTable views and PivotChart views are created in Access 2002, references to Office XP Web Components are automatically created so that developers can use the methods and properties associated with the Web Component object model in these views. These references are only created in Access 2002 files, not files in Access 2000 file format.
Access 97 Databases Must Still Be Converted
Unlike files in Access 2000 file format, Access 97 files must be converted to Access 2000 or Access 2002 file format in order to be used in Access 2002. The Convert To Access 97 File Format option is included in Access 2002 so that users can convert their files in Access 2002 and Access 2000 file format back to Access 97, albeit with reduced functionality. For more information on converting or enabling Access 97 files, see "About converting an Access file" in Access 2002 Help.
Sample Database to Assist You in Finding Access Files
Finding and determining the version number of the various Access database files in your organization can be a difficult and daunting task. To assist you, we have created an Access 2002 database that you can download. This database searches a drive or folder, including subfolders, for .mdb and .adp files. If any of these files are found, the results are stored in a table and a report is created which details information such as the number of files for each version and the total number of files found. You can easily customize this database to fit your own needs.
Conclusion
Access 2000 users can now upgrade to Access 2002 and continue using their existing databases and projects without interruptions or conversions. In this article, you learned about the considerations that developers need to be aware of when developing and using files in this environment.