Access lockes database while updating

[DMSJennifer]

Hello community,

I have the following problem and I hope some of you can give me the solution! 

Our database is connected to an Access server and has 3 schedules refreshes planned throughout the day. Unfortunately, this doesn't work most of the time. I get something like the following error message: 

"Database cant update, file already in use" 

It shows a table cousing this problem, but every time its a different table (and there is nothing wrong with the table). When I manualy refresh, the issue is gone.

I already searched the community and someone said: Dont use Access as your database. Unfortunately, this is not an option in my company, so Im looking for another solution to this problem. 

Can someone help me?

Kind regards, 

JenniferDMS

[pmreis · Accepted solution]

Hi @DMSJennifer 

That's a common issue when working with Access Data Sources. Here are a couple of ways to try to work it out:

1) Exclusive Access: Ensure an agreement that no other users or processes will be accessing the Access database during the pre-defined scheduled refresh times. This includes any other applications, scripts, or users that might be reading or writing to the database.

2) Compact and Repair: Regularly compact and repair the Access database. This can help in optimizing the database and potentially resolving any locking issues. You can do this by opening the database in Access and selecting "Compact and Repair Database" from the Database Tools menu.

3) Increase Timeout: Increase the timeout settings for the database connection in Power BI to give it more time to establish a connection, especially if the database is large or has complex queries.

4) Consider a Backend Upgrade: While you mentioned that moving away from Access is not an option, consider using Access only as a frontend and linking tables from a more robust backend like SQL Server. This can help alleviate many of the concurrency and locking issues associated with Access.

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

[DMSJennifer]

Hi @pmreis ,

Thank you so much for this list of solutions! We will try these and hopefully something wil work!
The 4th workaround is something we've been considering for a wile, but such a change takes time. 

JenniferDMS