dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Converting Access 97 Databases

Information and guidance on the conversion of Access 97 databases (.mdb file format) to Access 2013 and 2016 (.accdb file format).

Last updated on 2018-07-21 by David Wallis.


Preface

Microsoft has not provided support for Microsoft Access 97 for some years. No longer do you find tools for converting your Access 97 databases included in the latest versions of Microsoft Office.

DMW intends to continue to offer Access 97 conversion services for the foreseeable future — complete conversions or assistance to you as you attempt them yourself.


Converting Access 97 Databases to
Access 2010, 2013 or 2016

Access 2013 and 2016 have a feature to save a database in an earlier version:

Access Save Database As

But for their own use they lack features for converting databases created in formats earlier than Access 2007's .accdb file format.

So, to get an Access 97 .mdb file up to an .accdb format that Access 2013 and 2016 will recognise, you need to adopt an intermediate step. That is, in either Access 2007 and 2010, open your Access 97 .mdb and you are presented with the Database Enhancements options:

Convert Access 97 DB

Access will then present you with a Save As dialog box in which you give your converted database a file name.


Conversion Errors

In DMW’s experience, Access 2010 rarely completes the conversion of an Access 97 database without some errors:

Access Conversion Errors

Not all errors get logged for attention as part of any conversion process. I have examples of errors coming to light only when supposedly successfully converted databases crashed during use.

Some of these errors are due to certain objects that fail to convert. For instance tables, queries, and reports all may convert faultlessly, but a form or two may not. Macros are particular offenders.

If you plan to continue using Access 2010 for the foreseeable future, or to complete the move to Access 2016 — Microsoft recommend conversion to the latest version — then please contact me if you need assistance in preventing errors permeating conversions from earlier versions. Or read on.


Working Around Conversion Errors

Importing Into a New Database

One way to reconstruct a database that fails a straight conversion is to start a new one and import into it the objects from the original. You'll need to use Access 2007 or 2010 for this.

In a new database, go to the External Data tab and then click Access:

MSA Get External Data

In the Get External Data - Access Database dialog box, click Browse and navigate to your old database file. Make sure you select the Import tables … option before clicking OK to commence the import.

Getting Hold of the Data Using Excel

The most precious part of your Access 97 database is likely to be the data stored in it. So if your attempts at conversion have proved unsuccessful, another approach is to use Excel to extract the data from the database tables.

You'll need Excel 2007 or 2010, because later versions will not recognise Access 97 databases. So lay your hands on a PC with Office 2007 or Office 2010. Ordinary Office will be good enough — you don't need Office Professional, the one that contains Access.

In an empty spreadsheet, DataGet External Data → click From Access:

Excel Get External Data

Navigate to your database file and once you've opened it, choose the table you want to import. Repeat the process for each table. I recommend you start a new worksheet for each table.

With your worbook saved, you can use Access 2013 or 2016 to import the contents of its worksheets.


Slow-running Converted Access Databases

Some clients have been disappointed with the speed of their databases following conversion to Access 2010, 2013 and 2016.

DMW has built up experience of what should be taken into account if your converted database suffers from this problem.


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


An Opportunity to Improve Usability

If now you are upgrading to Access 2013 or later, then your database must have been in use for many years. So you will have had time to identify improvements you would like to make and to assess the relevance of some bits that nobody uses any longer.

Also, users will have views on what would make their database more relevant to their work than it is in its current form.

Probably, therefore, this is the best time to mount a review with a mind to redeveloping sections on the database as part of the upgrade.


An Opportunity to Gain Business Advantage

A long-standing database — in which a lot of time and effort has been invested collecting and inputting information — may not be yielding nearly enough return on all that investment.

For example, a company created their own database for recording orders received. The database held over seven years’ worth of orders. The company approached DMW to learn how they might take full advantage of all this information — reporting on, for example:

As part of your conversion process you might consider adding beefed up reporting to increase returns on investment in your database. Please contact DMW if you want someone with ideas from the outside to brainstorm the possibilities.


Help Converting Access VBA

There are changes in the Access VBA as you move up through the versions. Some of these changes are quite major ones. Some offer increased functionality that you may wish to exploit as part of a conversion upgrade.

The convert-database feature takes a stab at converting your VBA code. In DMW’s experience, conversion are very far from perfect. You might want to talk to us before you press the button or if you want Access VBA help in general.


Converting Access MDE Files

If your database is in MDE file format, there are utilities advertised that claim to unlock MDEs. Once unlocked, so the claim goes, you can make changes to your MDE.

DMW have tried one unlocker program that gets mentioned a lot on the Web. In those Access 97 MDEs that DMW have 'unlocked' using it, the majority of forms and reports will not go into design view. Furthermore, none of the VBA modules is rendered accessible.

So a conversion may be the best time to review the functionality of your database prior to offering to tender for the rewrite.

Help on hand for risky, obsolete, and perhaps decaying, Access 97 databases.