SSAS ACE.OLEDB errors

The problem:

While trying to import an excel file into an SSAS model, I kept getting the following error:

Failed to save modifications to the server. Error returned: ‘The provider ‘Microsoft.ACE.OLEDB.12.0’ is not registered.
The following system error occurred: Class not registered
A connection could not be made to the data source with the Name of ‘Text 2018 Tableau Usage’.

Initially, I assumed it was the server that was causing problems, so I created a model with an integrated workspace (ssas running locally in the background).  This rules the SSAS server I had been using out of the problem.

Visual studio only runs in 32 bit, which is why you’ll find it in: “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\”.

The installed version of office further complicates this.  This driver comes included with excel.  This is the version I had installed.  I thought at first that this might be the problem, but it turns out it was the same version I had working in another environment.  So this was probably a red herring.

You’ll need to install the Access Database Engine 2010 Redistributable in order to get the correct ACE driver.  You should end up with this:

Unfortunately, you can’t tell if it’s the 32 bit version installed, or the 64.  You may be able to intuit it from the registry.

For 64-bit:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\InstallRoot

For 32-bit:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\InstallRoot

But that doesn’t help, as an unsuccessful uninstall had left registry keys on my local machine.

The solution:

So it turned out the solution was to reinstall the 32 bit version, and uninstall it.  I then reinstalled the 64 bit version, but oddly didn’t have to specify /quiet on this install.  I did it on my local machine, so I could use an integrated workspace (spinning up SSAS when launching Visual Studio).  I then had to go to the SSAS server I’d installed, and repeated the same process.  So in short, the 64bit driver works while excel is 32 bit.

 

Credit to Meagan Longoria @ DataSavvy. This post has some good details: https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

 

 

Far better an approximate answer to the right question, which is often vague, than the exact answer to the wrong question, which can always be made precise. -John Tukey
The plural of anecdote is not data. - John Myles White

Recent Posts

RSS PowerBI blog

  • New Power BI Premium summary and workload metrics available in the admin portal April 22, 2019
    We have rolled out a new high-level summary metrics experience in the Power BI Admin Portal . This new experience replaces the four summary tiles you previously saw : CPU, Memory thrashing, Memory usage and DirectQuery with wide range of metrics measuring the summary of usage in the last 7 days to better portray the health […]
  • Live now, full session catalog for Microsoft Business Applications Summit 2019 April 22, 2019
    Time to get excited – the full session catalog for Microsoft Business Applications Summit is here. Explore every breakout session and workshop coming to the conference, taking place in Atlanta, Georgia June 10 – 11, 2019. Get ready to flex your skills – and build new ones – with all things Power BI and beyond. […]
  • E-Mail Subscriptions for Paginated Reports is Now Available April 19, 2019
    E-mail subscriptions is one of the key features used by millions of SQL Server Reporting Services customers today. Our latest release for “New Feature Friday” is the support of Paginated Reports in e-mail subscriptions in Power BI. Now, for the first time, you can schedule an e-mail subscription with a PDF attachment of your full […]