Power Query accessing Access database - intermittent error

[cr731]

I'm using Power Query to pull data from an Access database and about 20-25% of the time, I get this error,

[DataFormat.Error] Could not use ''; file already in use.

The only thing I can think to check is the Access Client Settings for Default Open Mode, which is Shared.

I cannot understand the underlying issue though, because most of the time that I receive the error, if I refresh a second time (sometimes it takes three attempts), it refreshes successfully.

Is there anything I can do to increase the reliability of refreshing queries from an Access file or other things I could check to find the root cause?

 

[anvg]

Hi
Try to use ODBC connection to your Access database

let
    source = Odbc.Query("DBQ=D:\Path\FileName.accdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;ExtendedAnsiSQL=1;
",
    "SELECT * From Table1")
in
    source

Regards,

 

[cr731]

Out of curiosity, why would this solve the problem?

 

[anvg]

Hi
Power Query uses oledb connection to Access database. When I used some connection in a table on worksheet sometimes I got equal mistake. But when I changed the connection to ODBC type that problem was hidden.
If you have OLEDB connection after refresh it you can see laccdb file. It is suggested that you have some lock of database file. But Excel already has not read data from that database.
If you have ODBC connection you can see laccdb in during refresh process. After it is finished the laccdb file is removed too.
Indeed I do not know all detail about connection. But using ODBC helps me.
Regards, Andrey.

 

[cr731]

Hi
Power Query uses oledb connection to Access database. When I used some connection in a table on worksheet sometimes I got equal mistake. But when I changed the connection to ODBC type that problem was hidden.
If you have OLEDB connection after refresh it you can see laccdb file. It is suggested that you have some lock of database file. But Excel already has not read data from that database.
If you have ODBC connection you can see laccdb in during refresh process. After it is finished the laccdb file is removed too.
Indeed I do not know all detail about connection. But using ODBC helps me.
Regards, Andrey.

As a follow up to this... is there any easy way to know the connection string when setting up a PQ query for ODBC? For instance - if I use Microsoft Query (via Data > From Other Sources > From Microsoft Query), which uses OBDC, after I choose a data source (such as MS Access), it then prompts me to choose an Access file and then the tables to import. In Power Query, there seems to be no user friendly way to navigate through the tables in the ODBC connection, unless I'm missing something?

 

[ssm2130]

For posterity:

It might be because you have more than one query to the database. Those queries area setup to update simultaneously, causing the error on some of the queries. To fix this, you may consider only querying the same table in access only once. Once in Excel, you can then run power query with the source being within Excel or power query rather than going back to the same table again.

 

[ssm2130]

If you cannot help having multiple queries to the same table, you could change the update frequency for each query. For example, if you have 3 queries, you could have them update at 2 minutes, 3 minutes, and 4 minutes. Or whatever your needs are 1 hour, 2 hours, 3 hours. This will prevent the queries from hitting the table simultaneously.