Set access data from sharepoint as source

[Clout]

Hello guys,

my organizatian has a access database in a sharepoint. Now I want to put the source of my report to this dataset in the sharepoint. I created the report in Power BI with the same database on my local computer. And after that, I want to refresh the data set automatically every few days in Power BI service.

I only found tutorial with excel, but is it possible with a access database?

[v-deddai1-msft · Accepted solution]

Hi @Clout ,

>>my company doesn't own any own server. Would this function too, if I install the gateway on my local computer to serve as a server?

Yes, only if your own computer can connect to access database in a sharepoint. And you need to keep your computer always on. Or the scheduled refresh will fail.

>>And how would the automatic datarefresh function, if I do this workaround with exporting the access table to sharepoint-list? So I have to export it every time again when I have new data?

Yes, every time you have new data, you should export the Access table to sharepoint list.

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Clout ,

No, it seems that you can't refresh dataset with datasource that is  access database in sharepoint.It will work in power desktop but not in power bi service.  Cause there is no Access drivers on cloud based service. In this situation, you need to use gateway with the access  drivers installed in the same server. Or you will fail with MessageMicrosoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. error.

There is also other workaround. If you just need some table in access database. Please try to export a Access table to sharepoint list via "Extenal data"->"More"->"SharePoint list" option in Access,  connect to the list using "SharePoint Online list" entry in Power BI Desktop, create report and publish report to Service.

There is a similar idea for your reference: https://ideas.powerbi.com/ideas/idea/?ideaid=2539d9db-1403-4e1f-813b-11e65cbaf3c2

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

Best Regards,

Dedmon Dai

[Clout]

Hello @v-deddai1-msft thank you for your answer.

So if I put the source trough a on-premise Datagateway in Power BI - Service, I could refresh it? 

But how to install the access driver on the server, if the Sharepoint is on Microsoft servers?

And is this workaround applicable if I already built my report with a local data source?

[v-deddai1-msft]

Hi @Clout ,

>>But how to install the access driver on the server, if the Sharepoint is on Microsoft servers?

You need to install access driver on the server where the gateway is installed.

>>And is this workaround applicable if I already built my report with a local data source?

Yes, it is also applicable for your local data source.

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

Best Regards,

Dedmon Dai

[Clout]

Hello @v-deddai1-msft 

my company doesn't own any own server. Would this function too, if I install the gateway on my local computer to serve as a server?

And how would the automatic datarefresh function, if I do this workaround with exporting the access table to sharepoint-list? So I have to export it every time again when I have new data?