DMW logo
contact@consultdmw.com

Converting Microsoft® Access 2007 Databases

How to convert Access 2007 databases to versions 2013, 2016, 2019 and Microsoft 365 of Access.

Last updated on 2021-01-26 David Wallis.


Preface

Microsoft ended support for Access 2007 on 2017-10-10, q.v. Office 2007 End of Support Roadmap. So, no more “Technical support for issues”, “Bug fixes for issues that are discovered” and “Security fixes for vulnerabilities that are discovered”.

As Microsoft recommends: “Because of the changes listed above, we strongly recommend that you upgrade as soon as possible”.

In this article, I offer guidance on how to attempt a conversion yourself. If you don’t fancy that, then I can attempt the conversion for you and I’ll be pleased to work with you if you want to make improvements as part of the process.

Using a remote connection, I can work for you wherever your database is located. For example, in the USA, in December 2018 I converted database files for a company in Colorado Springs; in January 2019, I completed files for a company in Valley Springs; at the date of this post, I’ve just completed a conversion for a company in Thurmont.


Preparing for the Conversion

Before using Access 2013, 2016, 2019 or Microsoft 365, I always check the Access 2007 database for faults and fix these so that the database runs smoothly under Access 2007. If you don't do this, and you attempt to carry over the database with those errors uncorrected, then you are going to jeopardise the success of any conversion.

Here are some pointers on things you should check. But please email contact@consultdmw.com if you need guidance on trapping errors during the conversion.

Code Compilation

This is a good starting point. Open the Visual Basic Editor and compile the database. Access will go to the first bit of code to which it objects. Fix that code and continue with compilations until Access offers no objections at all.

Error Handling

In my opinion every procedure should include code for the management of errors. In Excel Macro Error Trapping and Handling I suggest code that you could equally well apply to your Access Sub and Function procedures.

The On Error Resume Next statement I find is often included in a sloppy attempt to suppress an error, or series of errors, instead of addressing their cause properly. So be wary of the code as a whole if you discover any such statement where it can’t be fully justified.

Declaring Variables

Another, all too common, feature of sloppy coding, undeclared variables should not be allowed! So, have your code equipped to prevent their existence. This is very easy to achieve: make certain Option Explicit is the first line of code at the top of every Microsoft Class Object and Module in your Visual Basic Editor:

Option Explicit declaration in VBA module

Missing References

When compiling your code, Access might complain “Your ... database or project contains a missing or broken reference to ... ”. When you acknowledge the message, the Visual Basic Editor displays the References dialog box:

Access VBA References screen

To restore the missing reference;

  1. Remove the tick from the Reference marked MISSING
  2. Press OK to close the dialog box
  3. From the Tools menu, select Reference to open the dailog box again
  4. Scroll down, and tick the reference that is to stand in place of the missing one.

Often, a missing Reference is caused by changes made to the version of Access installed on your PC. Upgrading from Access 2007 to Access 2013, 2016, 2019 or Microsoft 365 may mean that one or more files that Access needs for your database are the wrong versions, left over from the database’s Access 2007 origin. For most such occurences, picking from down the list will fix things.


Completing the Conversion

The final stage in a conversion is to open the database in Access 2013, 2016, 2019 or Microsoft 365, and click Compact and Repair Database:

Access Compact and Repair ribbon button

If this action results in any errors, then you can address them in the version of Access in which you will now be using the database.


Help Converting Access VBA

If in your attempts to convert your database you’re faced with VBA that you don’t understand, then please give me a call. As with any programming, experience counts for a lot, so maybe I can quickly provide some guidance and possibly save you from some pitfalls.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

“Thank you so much for sharing this, David. It is so helpful to people learning like myself. I sincerely appreciate the help and wish you great success in all you do! Best regards, William.”

2018-08-23