By Allen Browne, July 2004. Updated June 2007
Converting from Access 97
What difficulties will you face if you convert to a more recent version of Access?
- Good news: Most Access 97 databases will convert to a later version and run with little or no change.
- Bad news: Most of the new features in Access 2000, 2002, and 2003 do not work properly, hinder productivity, degrade performance, or are of no use if you store your data in Access tables and use English.
If you are converting to Access 2007, you will also need the information in the Converting to Access 2007 article.
This article gives help in five areas:
Before you convert your databases to a later version:
- Consider a custom install of the new version, so you can instruct it not to remove your Access 97. You may need the old version if there are conversion issues.
- Go to support.microsoft.com, and make certain you have the latest service packs for both:
The bugs in the new versions are so numerous and devastating that it is absolutely essential to have both service packs - things like corrupting databases and duplicating autonumbers.
- your version of Office, and
- JET 4 (Joint Engine Technology is the engine handling Access tables and queries)
- In Access 2003, it is really annoying to be told your database is unsafe every time you open it. Lower your macro security setting:
Tools | Macro | Security | Low
If you do not see that menu option, right-click the toolbar area, customize, and reset the Menu Bar. For more information, see: Sandbox mode
The most common conversion problems are:
Database is corrupt
It is quite common to discover that a working Access 97 database is partially corrupt, and will not convert. Decompile:
- Make a backup copy of your mdb file.
- While Access is not running, enter something like this at the command prompt.
It is one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\MyPath\MyDatabase.mdb"
Then close this instance of Access.
- Open the database, and compact: Tools | Database Utilities | Compact.
- Press Ctrl+G to open the Immediate Window.
From the Debug menu, choose Compile and Save All Modules.
Solve any issues until the mdb does compile.
- Close Access 97. Open your new version, and try the conversion again.
If the database still does not convert, repeat steps 1 - 3 to decompile again. Then create a new (blank) database in your new version, and import everything as described in Recovering from corruption.
References do not convert
If the converted database has problems with Date(), Trim(), Left(), ... check your References. Specific issues:
- ActiveX controls including MS Calendar and MS Graph are likely to break, as the versions may be different.
- A reference to DAO 2.5/3.5 Compatibility Layer, will not work. (This is typical of databases originally written in Access 2 or earlier.) Using Access 97, remove the reference, and re-write affected code.
- If you import everything into a new Access 2000 or 2002 database, you must manually add DAO 3.6 Library.
- Some of the ADO objects have the same name as DAO objects, and may clash, e.g. Recordset, Field, Property,
To solve these issues, open a code window, and choose References from the Tools menu. More information on References.
Form does not convert
Very occasionally, you may find that a converted form fails to work in the new version, and it is not a corruption as discussed above. The converter sometimes mistakes a text box for a label - particularly where the text box is disabled and has no attached label. Of course, the converted form that has a label control bound to a field does not work.
If you strike this rare problem, delete the text box from the Access 97 form, and add it back after conversion.
- An MDE file cannot be converted. You must have the original MDB, as the MDE lacks the text version of the code.
- Special attention may be needed for replication, security, external data sources, or having >1000 code modules (including code-behind-form).
Ignorance is not bliss! The new versions will bite you if you are not aware of the bugs and problems.
- Name AutoCorrect: Every time you create a new database in Access 2000 and later, you must remember to turn off this mis-feature. The list of associated bugs is unbelievable. Once you turn this off (as you must), Access 2003 can no longer trace dependencies or propagate properties.
- Crashes: Subforms may cause Access 2002 and 2003 to crash, i.e. "... shut down by Windows. Sorry for the inconvenience." This happens intermittently if the LinkChildFields property refers to a field in the subform, but there is no control for the field. Open the subform in design view. Add a text box for the foreign key field, and set its Visible property to No. With this done and Name AutoCorrect disabled, Access 2003 may be more stable than 97.
- Themed controls: Under Windows XP, transparency does not work on tab controls, triple-state check boxes are not distinguished visually, and unattached labels flicker badly in Access 2003. In some design settings (e.g. query design, Relationships window) you be unable to select things or scroll.
The check box issue can be solved by setting its BorderStyle to Transparent. The tab transparency issue can be solved by unchecking Tools | Options | Forms/Reports | Use Windows Themed Controls. The other issues can only be solved by disabling the Windows XP themes: right-click the desktop, and set Theme to Windows Classic.
- Performance issues: The newer versions are slower than Access 97. Some factors such as Name AutoCorrect, Subdatasheets and long network path names cause severe degradation. See Tony Toews' Performance FAQ.
- Development time: Every time you create something - a table, a field, a form, ... - you must take the time to change these problem properties.
- Monolithic save: Whenever you save one object (such as a form), Access 2000 and later resave the entire thing. If there are many objects in your database, a save takes much longer. Two people can no longer modify different objects in a file at the same time. And you can no longer query the MSysObjects table for information on when objects were changed.
- Development environment: Unlike the integrated development environment in 97, later versions of Access are cobbled together with a version of Visual Basic. The separate windows are handy if you have dual monitors, but there are many weird problems with the way the windows and their help screens jump around, don't show correctly, have pop-ups that show when the main window does not, etc. It is even possible to corrupt a database if you have two of these later versions open at the same time, as Access gets confused about which VB window belongs to which project.
- Decimal field type: This new field type does not sort, even in a basic query.
- Conditional formatting: This is a useful new feature, but again the implementation is buggy.
- In-place design: Open a main form in design view, and you will be surprised to see that you can modify its subform in place. Nice idea, but by the time you fit the section bars, rulers, and scrollbars within the space, it becomes very frustrating - sometimes impossible - to even see the subform controls you want to modify. Right-click the subform control and choose Subform in New Window. (Not available in Access 2000.) Same for subreports.
- Help: The Help files in Access 2000 and later are vastly inferior. They are buggy, have no Index feature, and may be missing parts or entire libraries. Create a desktop shortcut to your Access 97 help file so you can still find things. Access 2003 is an improvement, but will search the Internet for help unless you change the setting under:
Tools | Options | General | Service Options | Online Content.
- Learning Access is more difficult: Some of the new features (such as cascade-to-Null) cannot be implemented through the user interface. Other features (such as setting the Seed of an AutoNumber) cannot be set with DAO. Other features (such as setting the
DisplayControl for a Yes/No field to a check box) cannot be set with ADO/ADOX. To master Access, you must learn all three approaches - interface, DAO and ADOX - and remember when each task can/cannot be done in each. It does not help that ADOX has so many bugs and inconsistencies between versions that it is effectively unusable.
- Old bugs: Most of the bugs that were present in Access 97 remain unfixed, such as these four Filter bugs, and missing records in SELECT queries. Reporting the incorrect record in the form's After Insert and After Update events is fixed, but the form events can now fire multiple times and illogically.
- Sorting is different on fields that contain a dash. For more detail, see:PRB: Sort Order Has Changed with Microsoft Jet version 4.0.
- Cannot update XLS. As part of the service packs for Access 2002 and 2003, Microsoft has removed the ability to update the data in an existing Excel file, "for legal reasons."
Most of these issues have been present for four years, through three versions and many service packs, so do not expect to see them fixed any time soon.
That leaves very few new features that work without problem, and are useful if you use Access tables to store your data:
- Form_Undo: If you ever locked or hid controls conditionally, you will appreciate being able to reset them in the Undo
event of the form.
- Reports: In Access 2002 and later, reports have an OpenArgs like forms, and can be opened in Dialog mode. The Printers collection is also much easier than the old PrtMip (though the
Port of the Printers may be shown incorrectly).
- Relationships: You can print the Relationships window.
Beyond Access itself, there are other features such as XML support.
Access 2002 and 2003 share the same file format, and can also use the Access 2000 format. Specify the format for new databases under:
Tools | Options | Advanced | Default File Format
So, which is best choice for Access 2002 or 2003 users? It makes little difference, and if you change your mind later it is very easy to convert. Considerations:
- To create an MDE, you must use your native file format.
- If some of your users have Access 2000, that's the obvious choice.
Before you release your project to users, use the lowest target version to compile it:
- An Access 2000 format file compiled in 2002 or 2003 may have intermittent problems if used in Access 2000.
- 2002 and 2003 are not binary compatible. An MDE compiled under 2003 will not work with the Access 2002 runtime.
If you have some Access 97 users who need to share data with others who have later versions of Access:
- Split the application, and use an Access 97 MDB as the back end.
- Create two versions of the front end: an Access 97 MDE, and an Access 2000 MDE.
- On the Access 2000 machines, turn off Record Level Locking: Tools | Options | Advanced. It is not supported by the Access 97 back end, and can cause problems.
- Expect the later versions to run a little slower, due to the JET 4 calls being converted to Jet 3.5.
Access 2000, 2002, and 2003 let you convert a database back to Access 97 format, through:
Tools | Database Utilities | Convert Database | To Access 97 File Format.
Naturally you lose any new JET/Access/VBA functionality that never existed in Access 97. There are also a few issues that the converter does not handle well:
- Occasionally, you end up with a form where a text box becomes a label control. Access 97 has no idea how to handle this label with its Control Source property, so you get errors until you track it down, delete the label, and replace it with a text box.
- Sometimes a report fails to convert back. The converter can get confused where a report has multiple subreports with the same Source Object (i.e. the same report used in different subreport controls.) If you strike this issue, try deleting the second subreport control from the report, and run the conversion again. You can easily add it back again after conversion.
- When you convert back from Access 2003, you end up with a reference to the Microsoft DAO 3.6 Library. This library is designed to work with JET 4 (Access 2000 and later), so you need to deselect this library and use the Microsoft DAO 3.51 Library instead. More information in the references article.