Converting Access Databases
Background information and guidance on the conversion of Access .mdb file format databases to the currecnt Access .accdb file format.
Last updated on 2019-04-14 by David Wallis.
The conversion of an out-of-date database to the latest version of Microsoft Access is, very rarely, a straightforward or error-free process.
I can assess your Access databases for what it will take to convert them; can perform the conversion for you; and will be pleased to work with you if you want to include improvements as part of the process.
Background to the Versions of Access
Microsoft ended mainstream support for Access 2010 in October 2015, but reports it will continue with extended support until October 2020. After 2020 there will be no support from Microsoft and no security updates or fixes.
2017 marked the very end of support for Access 2007.
2014 marked the very end to Microsoft’s support for Access 2003.
Support for Access 2002 finally ended years ago.
For Access 2000, years ago.
Access 97 support ended years ago, but I’m still receiving requests from all over the world to bring Access 97 databases up-to-date.
Potential Security and Confidentiality Issues
Any database that has not been created using Access 2010 or later, or has not been converted accordingly, has become a potential security risk.
Such a risk represents a failure to meet the requirements of certain auditing bodies — anything that relates to the Data Protection Act, for example (with the possible consequence of withdrawal or suspension of certification).
Microsoft recommends migration to the current version of Access before the end of their support period.
Converting Databases to Access 2010, 2013, 2016 or 2019
Access 2010 will recognise Access 2007 databases and provide for their conversion. Access 2013, 2016 and 2019 may not; but they’ll recognise Access 2010 conversions. So, if you need them there are stepping stones to bringing pre-Access 2010 databases up to Access 2013, 2016 or 2019 .
So, if Access 2013, 2016 or 2019 won’t recognise your .mdb, a first possible conversion route from .mdb-type to .accdb-type database is this:
.mdb ⇝ MSA 2007 ⇝ .accdb ⇝ MSA 2010 ⇈ MSA 2013/2016/2019.
Clients of mine, who attempted conversions themselves, reported that the process did not complete without errors. In a number of cases Access froze during the conversion and refused to budge.
Even if you complete the process, not all conversion errors get logged as part of the process. So you’re left not knowing exactly what might have gone wrong with the conversion. Some clients have told me of errors emerging after a converted databases had been in full use for a time.
Based on experience gained through numerous conversions, I’ve put together routines for trapping errors and dealing with them as part of the conversion process.
Menus, Toolbars and Ribbons
Access 97, 2000, 2002(XP) and 2003 had menus and toolbars.
Access 2007 replaced menus and toolbars with the user interface ribbon.
The ribbon features across Access 2010, 2013, 2016 and 2019 .
If the database you’re intending to convert has custom menus and toolbars, then be prepared to do some reading up on how to accommodate these.
Unless your converted database is for very short-term use only, I recommend going for a ribbon-only conversion, dispensing with menus and toolbars altogether.
User-level security provided for permissions to be allocated to users of your database determining what rights they have to work with or change the Access objects that comprise the database and the data it contains.
User-level security, included in many databases that I’ve seen and that were created using Access up to and including version 2003, was removed as a feature from Access 2007.
Access 2007 introduced new file formats — including “.accdb” — none of which would accommodate user-level security. So conversion of a secured database can throw up some challenges. Please contact me if you need help addressing some of the resulting issues.
Improving Your Database and What You Get From It
If now you’re upgrading to Access 2013, 2016 or 2019, or short-term to 2010, then you may have many ideas about improving it for its users and for gaining more out it in pursuit of improved business performance.
As part of your conversion process you might consider adding beefed up analysis and reporting in order to increase returns from your investment.
Perhaps, therefore, this is the best time to review the database with an upgrade in mind. Please contact me if you want someone with ideas from the outside to join in brainstorming the possibilities.
Help Converting Access Macros and VBA Code
There are changes in macro functionality, and in the Access Visual Basic for Applications (VBA) programming language, 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.
Access’s convert-database feature takes a stab at converting your macros and VBA code. In my experience, conversions are very far from perfect. In many instances conversion errors result from poor implementation in the original database, something with which a push-button conversion is not going to cope.
You might want to talk to me before you press the button or if you want help with programming in general.
Converting Access MDE Files
If your database is in the form of an MDE file, there are utilities advertised that claim to unlock MDEs. Once unlocked, so the claim goes, you can make changes to your MDE.
I’ve tried one unlocker program that gets mentioned a lot on the web. In those Access 97 and 2003 MDEs that I’ve “unlocked” using it, the conversion did not go perfectly and none of the VBA code residing in modules became accessible.
So now may be the best time to review the functionality of your database prior to offering to tender for the rewrite. Please contact me if you want to discuss.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.