Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

0
David1965

Power BI Service refresh issue with Microsoft Access data source

Submitted by on ‎02-28-2019 05:32 PM

I created a report in 64-bit Power BI Desktop using both 64-bit Excel workbooks and a 64-bit Access database.  I published the PBIX to Power BI Service where it successfully loaded the Report and Dataset.  The source Excel and Access files are stored in SharePoint Online and the data source credentials set to use OAuth2 successfully.  When attempting to refresh the data in Power BI Service the refresh fails and I receive the following message.

 

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

 

Status: Delivered
See more ideas labeled with:
5 Comments (5 New)
Comments
v-yuezhe-msft
Employee
Employee
‎02-28-2019 07:16 PM

@David1965 ,

To make the refresh work, you would need to export Access table to SharePoint online list, then connect to the SharePoint list in Power BI Desktop. There is a similar thread for your reference.

https://community.powerbi.com/t5/Integrations-with-Files-and/PowerBi-Auto-refresh-is-not-working-wit...

Regards,
Lydia

v-yuezhe-msft
Employee
Employee
‎02-28-2019 07:16 PM
Status changed to: Delivered
 
David1965
Regular Visitor
‎03-01-2019 05:57 AM

I did go down that path though the load to a SharePoint Online List is excruciatingly slow.  I am experiencing the same issue that others have reported where the status quickly goes to 33% and then slows down.  You can watch the slow increase of Items in the List being created in site contents though with ~1.5M rows in the table it will take an inordinate time to load.  I am going to let it attempt to load completely to see what happens when an actual refresh occurs from Power BI Service though I am equally concerned that other people have reported slow refresh times from SharePoint Lists.  Performing monthly new data loads that add will append to the List has the potential to be a slow process though possibly manageable.  If the Power BI refresh of the data is also slow then the whole concept of using SharePoint Lists is useless and frankly makes Power BI Service of limited value for any Microsoft customer that has embraced MS Access over the years to provide basic database capabilities where SQL Server is not an option.  What other options would be viable if MS Access isn't supported, SharePoint Lists are non-functional, Excel has the ~1M row limit and SQL Server isn't viable?     

David1965
Regular Visitor
‎03-01-2019 11:40 AM

I attempted to create a SharePoint List from the Access table and it was very slow in loading, ~1500-1700 records per minute.  After about two hours it ended in error at 173,400 records and I assume the failure was due to some timeout default where SharePoint severed the process.

David1965
Regular Visitor
‎03-05-2019 06:48 AM

The simplest workaround I found was to still maintain the data in the MS Access database and export the tables to comma delimited text files with the headers included.  The export is fast and Power BI Desktop easily pulls in the text files and transforming it with Power Query is straightforward.  Storing the text files in SharePoint Online allows for the Power BI Service to refresh against the data after a simple export, drag, drop and replace process to update the source data there.  The added benefit of inherent document versioning is a plus for rollback if necessary.  This seems like a silly workaround to have to do for two Microsoft products and while it works is not ideal.

Idea Statuses