Forum Discussion
Dataflow is failing with error database is in state that prevents from being opened or locked.
Hello,
Could you please help me understand where the issue might be coming from?
Context
I created a Power BI Dataflow that connects to an Access database through an on-premises data gateway.
The gateway is functioning correctly — its status is Online, and the service account has full access to the folder where the Access database is located.
The dataflow worked without issues until today.
The Access database is rarely opened by users; it mainly serves to refresh reports.
In the dataflow, I don’t perform any transformations — I simply import the tables as they are.
Issue
When I open the dataflow editor, I can view and refresh each table without any problems.
However, when I trigger a refresh in Power BI Service, it fails after approximately 15–45 seconds with the following error message:
“The database has been placed by user ‘Admin’ on ‘server’ in a state that prevents it from being opened or locked.”
Nobody else is accessing the database during these refreshes.
Additional Observations
When recreating the dataflow, I tested reducing the number of tables:
With 50 tables, the refresh fails every time.
With 25 tables, the refresh sometimes succeeds (about 2 out of 5 attempts)
Question
Could someone please help me understand why this is happening and what might cause the Access database to appear “locked” to Power BI, even though it is not being used by anyone else?
Thank you very much for your help,
Elena
The error likely stems from Access database locking behavior under concurrent or bulk access. Power BI Dataflows may trigger multiple parallel queries, causing Access to enter a state where it cannot handle simultaneous reads, even if no one else is actively using it.
Root Cause Summary
- Access databases are file-based, not server-based, and have limited concurrency support.
- Power BI Dataflows may open multiple connections in parallel during refresh, especially with many tables.
- This can cause Access to lock itself, especially if the database is large or fragmented.
- The error “placed in a state that prevents it from being opened or locked” is common when Access hits its internal resource limits or file locking conflicts.
Recommended Fixes
1. Split the Dataflow
- Reduce the number of tables per dataflow.
- Create multiple dataflows with fewer tables (e.g., 10–15 each) to avoid bulk access.
2. Compact and Repair the Access Database
- Use Access’s built-in Compact and Repair tool to reduce fragmentation and improve stability.
3. Avoid Simultaneous Access
- Ensure no background processes (e.g., antivirus scans, file indexing) are accessing the .accdb file during refresh.
- Confirm that no other Power BI refreshes or users are accessing the same file.
4. Use Scheduled Refresh with Gaps
- Stagger refresh schedules to avoid overlapping access.
- Avoid triggering multiple dataflows at the same time.
5. Consider Migrating to SQL Server or Azure SQL
- Access is not ideal for scalable reporting. If possible, migrate the data to a more robust platform that supports concurrent access and better integration with Power BI.
8 Replies
- tayloramy
Super User
Hi ekoroleva,
Are you able to confirm that no users are accessing the Access database? Can you access it direclty in Access?
The connection status in the gateway just tests if a connection can actually be estabilshed, it doesn't do a good job determining if the source will respond to a request (lame, I know).
Given your intermittent behaviour on the number of tables, I wonder if there's some sort of timeout happening on the gateway? Are you able to get any gateway logs for when this behaviour happened?
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.Hello @tayloramy,
Yes, I can confirm that none is accessing database. Plus I also verified that shared access is active, meaning that even if someone decide to open it, it should not block the process.
By timeout do you mean that connetion is unstable? or that error could be related to one of the tables?
In logs I don't really see any particular issue (the error appears like the file is already used - Failed)Elena
- tayloramy
Hi ekoroleva,
Are you able to open the Access database in Access directly?
It appears that there is some sort of file locking going on, so I think we need to figure out why the file is getting locked.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
Hello,
Yes, I am and I am able to see as well all tables there without any issue
- tayloramy
Hi ekoroleva,
Facinating. I hate Access.
So it works locally, but then tells you that it is locked when you try to copy data from it using the dataflow.
Here's a question, if you make a new report in Power BI Desktop that uses the access database as a source (nothing fancy, could just be a table), does that report refresh properly in desktop, and if it does, can you publish that report to the service and will it refresh properly there?
I'm wondering if there's an issue with dataflows.
The issue could also be with the gateway, there are two different paths that requests can take through the gateway, the new fabric path and the legacy power bi path. Power Query uses the legacy path.
Are you able to make a simple pipeline or copy job to test copying one table from your access database into a lakehouse in Fabric? This will test the new fabric path of the gateway.
It might also be worth opening a ticket with Microsoft, I'm grasping at straws here.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
- anilgavhane
The error likely stems from Access database locking behavior under concurrent or bulk access. Power BI Dataflows may trigger multiple parallel queries, causing Access to enter a state where it cannot handle simultaneous reads, even if no one else is actively using it.
Root Cause Summary
- Access databases are file-based, not server-based, and have limited concurrency support.
- Power BI Dataflows may open multiple connections in parallel during refresh, especially with many tables.
- This can cause Access to lock itself, especially if the database is large or fragmented.
- The error “placed in a state that prevents it from being opened or locked” is common when Access hits its internal resource limits or file locking conflicts.
Recommended Fixes
1. Split the Dataflow
- Reduce the number of tables per dataflow.
- Create multiple dataflows with fewer tables (e.g., 10–15 each) to avoid bulk access.
2. Compact and Repair the Access Database
- Use Access’s built-in Compact and Repair tool to reduce fragmentation and improve stability.
3. Avoid Simultaneous Access
- Ensure no background processes (e.g., antivirus scans, file indexing) are accessing the .accdb file during refresh.
- Confirm that no other Power BI refreshes or users are accessing the same file.
4. Use Scheduled Refresh with Gaps
- Stagger refresh schedules to avoid overlapping access.
- Avoid triggering multiple dataflows at the same time.
5. Consider Migrating to SQL Server or Azure SQL
- Access is not ideal for scalable reporting. If possible, migrate the data to a more robust platform that supports concurrent access and better integration with Power BI.
Thank you for your answer,
I think you are absolutely right regarding the cause, I am just surprised how it was working before...
I will accept this as solution
Elena
- tayloramy
Hi ekoroleva,
https://support.fabric.microsoft.com/
Click submit a ticket on this page.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
