Dataset Refresh error The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

[Frauke]

Hello,

I have a PowerBI-report that is publiced to a workspace, but refreshing the dataset gives the error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I've found a lot of posts on this error, but they haven't helped to solve the issue.

The dataset is located on Sharepoint, this is what shows under the dataset:

Off course I've tried installing the Microsoft Access Database Engine 2016 and it actually shows up in the Registry Editor as follows

But refreshing the dataset still gives the same error.

What I find strange is that is takes more than 15 minutes before the error shows. All this time it seems like it is refreshing and as long as it runs, it is stating refresh was succesfull, like below.

I'm thinking that maybe I'm installing the provider on the wrong pc, because the error states it's missing from the local machine. I'm installing the provider on my own pc, but this seems strange to me as the report is in a workspace and all pc's with access to the workspace should be able to refresh the dataset. So, logically I think it should be installed to the pc that hosts the workspace, but our IT-consultant can not answer me this. And I don't even know if workspaces have a host-pc or if I'm just making stuff up.

I've also found something about manually registering, but when I try this I get a failed to load error. And when I search for the .dll-files, I find nothing.

regsvr32 dboledb12.dll
regsvr32 dboledba12.dll

Does anybody have an idea how to solve this?

Kind regards,

Frauke Stevens

[ibarrau]

Hi. When dealing with xls, xlsb or access files you must use that driver. The driver will let you connect with power bi desktop on the machine you have installed. When you published to service it's different. Service can't understand the files and install a driver. The way to go is using an On premise Data Gateway. The machine or VM in which you have installed the Gateway, there it is the place to install the driver ACE OLEDB. The gateway will understand the file for Power Bi Service. Once installed, make sure you reboot the machine to let it work.

I hope that helps,

[Frauke]

Dear Ibarrau,

Thank you for your reply.

You're most probably right, but hope you can help me a bit further, because I'm still not sure how to proceed.

As you can see in the first picture in my original post it states that I don't a gateway and also that the slider to turn on the option to use an on-premise data gataway is disabled. What should I do to make this available.

And next, I have no clue how to setup an on premise data gateway, and on which machine I should this.

And also I'm wondering if this would allow other pc's to refresh the dataset or just the one with the on premise data gateway.

Sorry for sounding like a total nitwit, it's just not my field of expertise. I create the PowerBi-report and have published it to the workspace that has been set-up by our IT-partner, but they don't seem to know how to fix this either.

[ibarrau]

The idea of a Gateway is installing that on a Machine that will be turned on when refresh happens. It's usually recommended on a VM or a Server that is turned on 24 hours a day to let you refresh any time.

If you don't have IT support or VMs to install it, you can install it in your personal environment. It will refresh while your PC is turned on. It's not the best practice because the entire solution depends on your PC, but it's a start.

You can learn more about the gateway with this two videos:

Get started: https://www.youtube.com/watch?v=Vlv1tbuZNcM

GW new look: https://www.youtube.com/watch?v=L18HEAnBqTo

I hope that helps,

[Book_Piramid]

Hello,

What about the Microsoft Fabric?  How to make a gateway that connects to it.
I try to use Dataflow that extract data from .xlsb in Sharepoint and yes error message showed up require me to install ACE OLEDB 

(I know the "save as .xlsx" solution but I want to connect dataflow with .xlsb)

Any solution to solve it?

Thank you,

Best regards,

[ibarrau]

Hi. It's the same gateway for Fabric than for Power Bi.

If you have more troubles and issues you need help, please consider writing a new post so we can keep track just in case it's a different solution.

I hope that helps,