Help! Access database source error when using Append or Merge in Power BI Desktop Power Query

[AliPoTD]

Hi,

Can anyone help me as I've had a big issue suddenly start today and I can't understand what is happening.

I have an existing report which pulls data from an Access db held on a server. 

Now, when I refresh the table within Power BI Desktop it it seems to take the data from a temporary .accdb file held on my local disk, instead of using the path directory set in the Power Query source.

For example, my power query source is as below

but when I refresh the table, I get the below

I've narrowed down the problem to the use of either Append or Merge. For example in the above 1 intake tbl, if I remove all rows of commands so that just the first three run (from the Append command onwards deleted), it runs as normal.

The big problem I have is that I've had to make some changes to the report and have uploaded it to the Power BI Service but the Scheduled Refresh is falling over due to security reasons. I can't get around this as the Data source settings for the table show the correct path as per the Source code in Power Query. For some reason it's writing/ reading the file path to my local disk when either append or merge is used. Why???

Big thanks to anyone who can help to get this resolved as I've got to deliver the report changes asap.

[AliPoTD]

Many thanks for your quick response. 

Access causes me no end of headaches! But fortunately we are in the process of transferring to other DB software so hopefully I won't have to endure this for much longer!

I will check that the gateways are still up to date or whether any updates/ maintenance is needed.

[lbendlin · Accepted solution]

 For some reason it's writing/ reading the file path to my local disk when either append or merge is used. Why???

Probably because Power BI was not able to write the lock file to the file share location.

Scheduled refresh will fail for more than security reasons.  You would have to use a gateway, and you would have to install the legacy 32 bit driver on each gateway cluster member.  Ideally you would want to move away from an Access data source to something a bit more contemporary.