cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clout
Helper III
Helper III

Set access data from sharepoint as source

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?

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

v-deddai1-msft
Community Support
Community Support

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

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?

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

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors