DMW logo
tel 01732 833085
tonbridge · kent · UK

Converting Access 2007 Databases

Information and guidance on the conversion of Access 2007 databases to Access 2013, 2016 and 2019.

Last updated on 2009-04-14 David Wallis.


Preface

Microsoft ended support for Access 2007 on 2017-10-10. 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, files for a company in Valley Springs; at the date of this post, I'm working on files for a company in Boone.


Preparing for the Conversion

Before using Access 2013, 2016 or 2019, 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 contact me 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

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:

VBA References

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 or 2019 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 or 2019, and click Compact and Repair Database:

Compact ann Repair

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

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 —

Invoice

Thanks, in anticipation.

SSL Cerification

“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