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: 

View all the Fabric Data Days sessions on demand. View schedule

KHaber11

Cannot Refresh Power BI Desktop: "OLE DB or ODBC error"

Submitted by on ‎03-06-2018 12:53 PM

I am having trouble refreshing Power BI Desktop.

 

My dataset is an Access Database that has linked connections to SharePoint lists. 

 

Previously, before the February 2018 update, I was able to refresh Power BI with this Access Database.  After the update, I am unable to refresh data, and recieve the following error message: 

 

"OLE DB or ODBC error: [DataFormat.Error] the database has been placed in a state by user '' on machine '' that prevents it from being opened or locked.."

 

Some notes about the Access Database:

  • I exit the Access Database entirely prior to attempting to Refresh in Power BI
  • The database is set to Shared mode.   
  • In Access Options, -->Current Database --> "Caching web service and Sharepoint Tables", the following option is selected to Cache the list data: "Use the cache format that is compatible with Microsoft Access 2010 and Later."
    • When this option in Access is turned off, the Power BI refresh works...However, my data set is very large, and it takes too long to update Power BI if the Access data is not cached

After searching the message boards, I have not found anything to fix this issue.  Any help is greatly appreciated.

Status: Needs Info
See more ideas labeled with:
3 Comments (3 New)
Comments
v-jiascu-msft
Microsoft Employee
Microsoft Employee
‎03-07-2018 01:52 AM

Hi @KHaber11,

 

Firstly, why not using the Power BI Sharepoint List connector directly?

1. Is the Sharepoint list in the Sharepoint Server?

2. What's the size of the dataset?

Cannot_Refresh_Power_BI_Desktop_OLE_DB_or_ODBC_error

 

Best Regards,

Dale

Vicky_Song
Impactful Individual
Impactful Individual
‎03-07-2018 01:52 AM
Status changed to: Needs Info
 
KHaber11
Regular Visitor
‎03-07-2018 07:15 AM

Hi Dale,

 

Thanks for the response. 

 

I can use the Power BI Sharepoint List connector directly and the data will load fine.

 

However, when my Access DB caches the Sharepoint List data, it essentially runs at the speed of a local table, and thus is much faster than drawing the data in/making the connection from SharePoint.

 

 

Additionally, I have several queries in my Access Database that manipulate/calculate the SharePoint data, and I am bringing these queries into Power BI.  Just makes it easier if I can keep everything consolidated into one refresh.

 

My dataset consists of several tables >100k rows--so very large and very time consuming to refresh.

 

One interesting note...I can refresh all of the Cached tables individually within Power BI--and this works fine.  Its just when the tables are refreshed simultaneously by clicking the refresh button that the error occurs (note--I have turned off the "Enable parallel loading of tables option" within Power BI, but this still does not fix the error).

 

Idea Statuses