# Power Query accessing Access database - intermittent error



## cr731 (Apr 1, 2016)

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 (Apr 1, 2016)

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 (Apr 19, 2016)

Out of curiosity, why would this solve the problem?


----------



## anvg (Apr 19, 2016)

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 (Jul 13, 2016)

anvg said:


> 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.
> ...



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?


----------

