Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

[swan1099]

Hello community,

Our small company primarily uses an access database to manage our business, to which I have hosted in our sharepoint online site.

I'm looking to use PowerBI Desktop to design a multitude of reports, and then upload them to the PowerBI Service for our internal users to use. I need to it be refreshed on a schedule.

Does anyone have any advice as to how I should set this up?

Thanks!

[Seth_C_Bauer]

@swan1099 You'll need to install the on-premises data gateway. I would create all your reports in Power BI Desktop first, this will provide you flexibility in so many ways. Create and use a Group Workspace to publish the reports to, this will allow multiple users to be able to manage the reports instead of just one person.

When you deploy the Desktop reports, a dataset will be created for each set of reports you publish. The dataset can be scheduled to refresh up to 8x per day, this is set up and done in the Service.

There was a new way to easily embed into SharePoint Online, but it does require certain version etc., but it's really straightforward. Otherwise you would need to do some custom DEV to embed.

[swan1099]

Hello @Seth_C_Bauer,

Thank you for your comments, I do appreciate the advice. I've downloaded & installed the gateway, and for the life of me, cannot get the gateway to add our sharepoint site as a data source. 

Any thoughts on what may be holding me up? It looks like I'm being hung up in the username/password area, but I've tried my o365 credentials as well as my ActiveD credentials...it won't accept either?

Thanks!

[Seth_C_Bauer · Accepted solution]

@swan1099This quick tutorial might help

[swan1099]

@Seth_C_Bauer This appears to be exactly what I was looking for -- I can do some trial and error I suppose, but will this method of connecting to the data not require a data gateway? That would be ideal!

[v-yuezhe-msft]

Hi @swan1099,

How do you host Access database in SharePoint Online? According to my knowledge, all of Access tables become SharePoint lists, and records become list items.

This way, you can connect to SharePoint Online list from Power BI Desktop, create reports there and publish them to Power BI Service, it doesn't require gateway when you refresh the dataset in Power BI Service.

Thanks,
Lydia Zhang

[swan1099]

Apologies, perhaps I used the incorrect terminology. I have the access database stored in our sharepoint site as a document.

@v-yuezhe-msft

I've followed @Seth_C_Bauer's advice, and it worked as far as connecting to the sharepoint data and the ability to refresh without the data gateway.

I've scheduled a refresh, but now I'm recieiving this error upon scheduled and forced refreshes:

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 2010 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.

I can't seem to understand though why I am getting this, as I have 32 bit office, Powerbi Desktop, & Microsoft Access Database Engine 2010 32 bit installed.

Any thoughts?

[v-yuezhe-msft]

Hi @swan1099,

Power BI Service can only leverage 64 bit drivers, you should have no issues refreshing 64 bit Access database in Power BI.  There is an similar thread for your reference.

Thanks,
Lydia Zhang

[swan1099]

Hi Lydia,

So even though the desktop PowerBI app can refresh 32 bit access files, the PowerBI Web service is unable to at this time?

Thanks,

-Blake

[v-yuezhe-msft]

---

@swan1099 wrote:

Hi Lydia,

So even though the desktop PowerBI app can refresh 32 bit access files, the PowerBI Web service is unable to at this time?

Thanks,

-Blake

---

Hi @swan1099,

Yes, it is by design.

Regards,
Lydia Zhang

[ShakhovBoris]

Good day.

I have the same error, but I`am trying to update 64-bit accdb from sharepoint folder.

What can be wrong?

[michaeltrepte]

Hi,

same issue here. Did someone managed to get the Gateway active while having the access db file stored on SharePoint?

thx,

michael

[Anonymous]

Curious to know if this is already resolved as I am facing the same exact scenario. Any advice?

[michaeltrepte]

Hi

same issue here - did someone managed to get the Gateway active while having the access db on SharePoint?

thx,

michael