Reply
AliPoTD
Helper III
Helper III
Partially syndicated - Outbound

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

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

AliPoTD_1-1725488938409.png

but when I refresh the table, I get the below

AliPoTD_2-1725489002611.png

 

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.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Syndicated - Outbound

 

 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.

View solution in original post

2 REPLIES 2
AliPoTD
Helper III
Helper III

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

 

 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.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)