Forum Discussion

swan1099's avatar
swan1099
Icon for Helper I rankHelper I
9 years ago
Solved

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

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    swan1099This quick tutorial might help

swan1099's avatar
swan1099
Icon for Helper I rankHelper I
9 years ago

Anonymous 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!

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    swan1099
    Icon for Helper I rankHelper I
    9 years ago

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

     

    Anonymous

     

    I've followed Anonymous'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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        swan1099
        Icon for Helper I rankHelper I
        9 years ago

        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