Cannot import excel files to SSMS, but possible using SQL Server Import and Export Data

Question

Hi All,

I have issue to import the excel files to SSMS. I used:

SSMS : v18.9.2
SQL Server 2017 64 bit
Microsoft 365 (64bit)
Windows 10 Enterprise 64 bit
Installed:

• Access database engine x64 12.0, 15.0, 16.0

Everytime I want to import excel files via SSMS, it shows error while I believe I already installed the access database engine

Also the same, when I using excel version 2007-2010 or 2013 to import.

The more weird things are, it is possible to do it from "SQL Server 2017 Import and Export Data (64 bit)". But it painful way to do every data import using those apps and not in SSMS itself.

Any solutions?

Thanks

Answer 1

SSMS is a plugin for Visual Studio and that is a 32 bit application; 64 bit version isn't available.
And so SSMS starts the 32 bit wizard and the 32 bit wizard requires a 32 bit ACE data provider to work.

In SSM menu "Option" => "External tools" you can create a shortcut to start the 64 bit wizard.

Answer 2

Hi @Randy Adhitama ,

To fix this,

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 2017( 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 it is not work, please try the other solutions from the blog How to Fix The “Microsoft.ACE.OLEDB.16.0 Provider is not registered on the local machine.”

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 3

doesnt work anymore. Can anyone offer another solution to this?

Ive seen this same solution on every forum and I have tried to do this over and over with absolutely no change in error message or success in any capacity.