Can not longer use import and Export Wizard to import Excel version 2016 after upgrade to Office 365. Error - "..OLE DB provider Microsfoft.ACE.OLEDB. 16.0 is not registered..."

Pat Mesop 1 Reputation point
2022-06-15T22:04:51.447+00:00

We recently upgraded from Microsoft Office 2016 to Office 365. We use SQL Server 2012 and in the process of importing data from Excel 2016 into
database tables, I find that I can no longer use the Import and Export Wizard to import data from Excel 2016 into SQL Server database tables. When I attempt to do so I receive the error,

'The requested OLE DB provider Microsoft.ACE.OLEDB. 16.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Package1.dtsx 0"

It was working fine before the upgrade to Office 365. Obviously the upgrade replaced OLE DB provider Microsoft.ACE.OLEDB. 16.0 with the equivalence for Office 365 and there is obviously no backwards compatibility. Not sure what to do as I have a lot of Excel 2016 files data to import. Is there a configuration change that can make this work. What do I have to do to import older version of Excel in my Office 365 system using import and export Wizard.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,814 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-06-16T07:10:00.033+00:00

    Hi @Pat Mesop ,

    To fix this error, first thing first, install “Microsoft Access Database Engine 2016 Redistributable.”

    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    And then also check the version of Excel and SSMS. They need to be same, like 32 vs 32 or 64 vs 64
    SSMS is a 32 bit client, if your Microsoft Excel is 64 bit. you may need to install the 64 bit version of the Microsoft Access Database Engine 2016 Redistributable and try opening – the 64-bit “SQL Server Import and Export Data” directly. Go to Start Menu -> Microsoft SQL Server 2012( your SQL server version) -> SQL Server Import and Export Data (64-bit)> Open the application and try to import data using the “Excel 2016” option, it should work fine.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.