cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pkoetzing
Advocate III
Advocate III

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: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

 

Of cause PBI Service is not even supposed to use the local machine, but to connect directly. I've got no issues refreshing directly from a csv-file on SharePoint.

 

Is this actually a bug or just a missing feature, or is there any workaround available?

 

/Peter

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi Lydia,

 

I can confirm that your solution is working! But frankly it's no longer an Access database we are connecting to. I could as well export to csv files and move them to SharePoint. Again the online refresh is working. But I'm loosing all the advantages from having >100 tables in one container and beeing able to update single records ...

 

Thanks for your help!

Peter

I'm afraid I agree - this isn't a solution (access on sharepoint isn't opening).

I'm having the same issue reading access databases hosted in sharepoint libraries. (We are running a premium SKU for our powerBI if that is part of the equation)

 

I can access and pull the data correctly out of the file from powerbi desktop.

 

I publish the file and check the settings... no on-premise gateway selected, oauth2 authentication credentials are set on the dataset. But if you try to refresh you get what looks like the old MDAC missing error (except since this isn't running on the gateway we control, we don't have a server to add the missing access components to.)

 

Any suggestions how to make the hosted report (dataset) read the database?

Thank you

Jennifer

 

Having similar issue and cannot connect from PowerBI dataflow (service) to the Access DB file on Sharepoint, even after installing Access 64bit driver on an enterprise gateway and applying the gateway in the dataflow.

 

Current workaround is to connect dataflow to the same Access DB file on a network drive, through the gateway (with Acces 64bit driver installed).

@chaz2jerry  I realiased this was a very old thread, marked as resolved (because they had a workaround for the original poster).

I started a discussion https://community.powerbi.com/t5/Power-Query/reading-access-databases-hosted-in-sharepoint-libraries... to focus on connecting to the files as-is rather than having to ETL out to somewhere else first.

I'm curious how you managed to route your sharepoint connection via the on-premise gateway though. Our gateway couldn't support Oauth2 for sharepoint even last week. (just had the option pop up for us during testing this morning!)  See you on the other thread!

v-yuezhe-msft
Microsoft
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Greg_Deckler
Super User
Super User

So...you have an Access database in SharePoint Online?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors