Forum Discussion

pkoetzing's avatar
pkoetzing
Advocate III
9 years ago
Solved

PBI Service refresh from MSAccess on SharePoint?

Hi,   My dataset is connected to a MSAccess database on SharePoint. Refreshing from PBI Desktop works fine, but refreshing from PBI Service fails with the following message:   Microsoft Access: T...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    pkoetzing,

    I export a Access table to sharepoint list via "Extenal data"->"More"->"SharePoint list" option in Access, after that I connect to the list using "SharePoint Online list" entry in Power BI Desktop, create report and publish report to Service.

    This way, I click "Refresh Now" in Power BI Service to refresh the dataset, everything works well. Could you please perform the above steps in your scenario and check if it is successful?

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

pkoetzing,

What bit version of Access database do you have?  

And how do you host Access database in SharePoint? As per my knowledge, all of Access tables become SharePoint lists and records become list items. Do you use gateway in your scenario?

Regards,
Lydia

pkoetzing's avatar
pkoetzing
Advocate III
9 years ago

Hi Lydia,

 

I never thought of an .accdb-file bitness property. On my local machine I'm using 64bit PBI Desktop and a 64bit Microsoft Access database engine 2010. This works perfectly fine. It's the PBI Service that doesn't understand the MSAccess format.

 

I don't want to use the gateway. I configured PBI-Service to directly access the database file on SharePoint "from cloud to cloud". You're right, everthing on SharePoint is managed in lists, even the files, and my database is just one item in this list. But remember: PBI Desktop has no problem accessing the tables in this "database item" in the cloud, but the PBI Service has. So from my understanding the PBI server running the requested Refresh doesn't have the proper database engine installed.

 

And I don't know what to do about that.

/Peter

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    pkoetzing,

    Could you please tell us which connector do you use to connect to the Access database stored on SharePoint Online in Power BI Desktop?

    Regards,
    Lydia

    • pkoetzing's avatar
      pkoetzing
      Advocate III
      9 years ago

      Hi Lydia,

       

      I'm actually using the "SharePoint folder" connector. E.g. in the query I have

       

      let

          PowerXpert = "https://tenant.sharepoint.com/sites/Departments/PowerXpert/",
          Solutions = PowerXpert & "ModelData/3-SRCPS/2017-05-23 Release 9/Solutions/",
          Source = SharePoint.Files(PowerXpert, [ApiVersion = 15]),
          File = Source{[Name="Fuel_Report.accdb",#"Folder Path"=Solutions]}[Content],
          Import = Access.Database(File),
          Fuel_Prices = Import{[Schema="",Item="Fuel_Prices"]}[Data],

          ...

       

      Before I tried the "Access database" connector, but couldn't get that working on SharePoint.

       

      /Peter