MS Access dataset on Sharepoint - "Microsoft.ACE.OLEDB.12.0 provider is not registered"

[bigrods]

Hi,

Hoping someone could help:

I have a report with a dataset which is an MS Access file that is saved on Sharepoint. When I try to refresh the dataset from the Power BI service I get the error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.</pii>. ;Microsoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. 

I have installed the Access Distributable file on my laptop (where the Access file was created) - but we are using Office 365 for everything so there is no physical Sharepoint server as such to install it on there??! 

If I re-publish the report from Power BI Desktop on my machine it works fine, but the issue is when trying to refresh (or creating a scheduled refresh) in the Power BI service.

I've seen other posts on this, but they didnt really solve the issue (and I'm not sure the users were using O365)

Are there any viritual Sharepoint settings that could be checked at all?!

Many thanks

[v-yetao1-msft]

Hi @bigrods 

You can put your suggestions in Ideas .

https://ideas.powerbi.com/ideas/

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @bigrods 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[bigrods]

Hi @v-yetao1-msft 

Thanks for the reply; I am using a Personal Gateway at present and this has solved it for now.

Sadly it's not obvious this is what is required to do; all other files held on Sharepoint can be connected to so you would assume Access db's would be fine also and this seperate method of connection is not required. Hope they manage to fix it.

But thanks for helping!

[v-yetao1-msft · Accepted solution]

Hi @bigrods 

How did the data on your MS Access file come from? How do you connect in Desktop ?

If you created your datasets and reports based on a Power BI Desktop file, Excel workbook, or comma separated value (.csv) file on OneDrive or SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh. In this case, you can refresh without configuring the gateway. Otherwise, you need to configure the gateway to help refresh .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yetao1-msft]

Hi @bigrods 

(1)Did you configure the gateway in your machine ? If no ,you can refer to the link to configure the standard gateway .

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

(2) Did you install the 64bit Access database engine in the machine where the gateway has installed ? If no ,you can install the access database engine in below link .

https://www.microsoft.com/en-in/download/details.aspx?id=13255

(3)After you finishing all the options above successfully ,you can refresh normally in Service .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bigrods]

Hello Ailsa,

The database is held in the Cloud on Sharepoint so there is no need to configure a Gateway. Or is there?

Thanks

[Krishna09]

Hello,

My .xls file is also in Sharepoint. 

when creating Power query(Dataverse -dataflow) using this file. It gives below error.

How did you install in sharepoint? If you have solved it please let me know how did you solve it.