Thursday, December 1, 2016

Import Access 97 MDB Data Using Access 2016

Some people may still be using an Access 97 MDB database and then realize they can no longer open their database when they upgrade to Access 2013 or 2016. According to this MS article, support for Access 97 MDBs was removed in Access 2013. The recommended method for upgrading an Access 97 MDB database to a ACCDB file is to use an earlier version of Access like Access 2007 or 2010. However, if this is not an option for you, i.e. you don't have a copy of Access 2007 or 2010 or do not know anyone with a copy of Access 2007 or 2010, then there may be another solution.

CAVEAT: As the title of this article implies, the following solution only applies to migrating the data from the MDB into a ACCDB.

I came across this solution while trying to help a member at UtterAccess. I was thinking one might be able to trick Access by using Excel to import the old data first before importing it again into Access. Unfortunately, I guess the process of doing so was not as simple as it may seem.

In looking around Microsoft Answers forum threads, I found a post by Kevin M. as quoted below (I hope he doesn't mind me reposting it here):

I have "laundered data" through excel 2013 as follows
open excel and select the "data" tab.
select "from Access" and then copy the path to the data sourse from under the "connection" tab.
Then select the "provider " tab and then select "Microsoft Jet 4.0 OLE db provider"
Then select "Next"  (dont select "OK") and paste the path into the database name field (or browse to it) Then select "OK"
This should get an Import Data dialog box and you can click ok to paste the data into excel
good luck

As verified by the UA member mentioned earlier, although this trick was originally offered for Access 2013 users, it still works with Access 2016.

If anyone has found other ways to accomplish the same thing, I would invite you to share it here by posting a comment.

Thank you!