excelms-accessoledboledbconnection

How do I read data from an old Access .mdb file with Excel 64-bit

Have an old Access 97-version .mdb file I read the data from VBA in Excel 32-bit with the connection string "Microsoft.Jet.OLEDB.4.0"

Now Excel has been updated to 64-bil version and the connection to the database file is not working. I have tried to change to "Microsoft.ACE.OLEDB.12.0" but the error "unable to open file created in previous versions" appears.

So is there any option to make this work?

Converting the .mdb file to a newer version is not an option.

Regards

Solution

  • You can open these if you install the Access 2010 database engine, which has 64-bits support and support for Access 97 files. The connection string you had should work, provided you have the Access 2010 database engine. As the Access 2010 database engine was the only version with both 64-bits support and support for Access 97 mdbs, any other version won't do.

    Note that you cannot simultaneously install Office with Access included, and the Access Database Engine, so if you want to open these in Excel you need to install an Office version which doesn't include Access.

    Also note that Access 97 files are out of support for over 10 years, and the Access 2010 database engine has also been out of support for many years. You'll need to track down an unofficial source, as these are no longer provided by Microsoft.

    For more details on the compatibility of mdb versions and Access versions, see https://www.loc.gov/preservation/digital/formats/fdd/fdd000462.shtml.