Access Database file interrupts the refresh every day because the "file is in use when it isn't

[WorkHard]

I have a mix of different datasources for a report and one of them is an access database file.

This file is only accessed by me so no one else is using this file at 6 AM when I perform the refresh. Regardless, this report fails to refresh because of this file every other day because the file is in use.

This is not user error. I do not have this file open, when I go into the folder, the file is not locked. If I refresh in the desktop, I get the same error.

I am using 3 tables from this file and I notice that they all refresh separately. If I hit refresh and look in the folder I can see the file being locked and unlocked (a new hidden file appears and disappears) in quick succession for a few seconds before I get the "file is in use" error.

What gives. Is this because I'm pulling 3 tables? Should I introduce a 10 second timer between each table refresh?

Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support. 

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"<pi>Could not use 'DatabaseDB.accdb'; file already in use.</pi>"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"DatabaseDB.accdb"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataFormat.Error"}}],"exceptionCulprit":1}}} Table: Table1.

[WorkHard]

Interestingly, if I actually have the file open, the refresh works just fine. So now I believe that PowerBI is attempting to access the same file 3 times in a row and locks itself out. Yes, the file is on a network share.
I"ll disable the "Enable parallel loading of tables" as that seems like the easiest solution but I wish this option would extend to the service. 

[v-jianboli-msft]

Hi @WorkHard ,

According to my knowledge, the error is caused by the Access file.

Know the cause of ‘Access database already in use’ error

1.Damaged Access database file/corruption in Access database file

2.User does not have ‘create’ rights for the folder in which the database is present, and he opens the database. The MS Jet database engine does not create the locking information file (LDB in Access 2003 and in earlier Access versions and LACCDB in MS Access 2007 and 2010 versions, which is essential for multiple users to open the database.

For more details, please refer to:

Resolving Access database error ‘file already in use’

Error in Access when opening a database on a network file share

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[otravers · Accepted solution]

Yes, it does look like refreshing tables in parallel locks the Access file, as discussed in this thread:

https://community.powerbi.com/t5/Desktop/Error-refreshing-Access-database-data-tables-on-a-network-i...

The thread above gives a solution for Power BI Desktop, but I'm not sure you could enforce serial refreshing in the gateway settings. A couple of workarounds come to mind:

1. Create three separate dataflows for your three Access tables and orchestrate their serial refreshing via Power Automate. This would be my initial attempt if I had to quickly solve the issue myself.

2. Use the Dataverse connector in Access so that Dataverse is your source for Power BI. This is probably what I'd pursue if I had time and wanted to get other benefits of the Power Platform and/or start thinking about eventually migrating everything to the cloud.