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!

7 comments:

  1. Thank you very much for this short and easiest method to import mdb data into access 2016.
    I tried lot of options before this. But most of them suggested to uninstall access 2016, install the lower version and then convert into accdb format.Some suggested to download some applications also. by far, this is the best way to do it, without doing any installation. Thanks again.

    ReplyDelete
    Replies
    1. Hi. You're very welcome. Glad to hear you were able to use this approach. Cheers!

      Delete
  2. it doesn't seems to work with office 365, any update?

    ReplyDelete
    Replies
    1. Sorry, I don't have any update at the moment. I'll ask someone with O365 for some ideas. Do you still see the Jet 4.0 driver as an option in O365?

      Delete
    2. In the office 365 Data tab, Get Data->From Other Sources->From OLEDB Click the build button. Then select the Microsoft Jet 4.0 OLE DB provider. Click next and find the database in the connection tab. Then OK a couple times, select the tables you want and load.

      Delete
    3. Hi Dale. Thanks for the update!

      Delete
  3. Thanks for this - really helpful!

    ReplyDelete