Using a linked Excel file in Access and having trouble with scheduled refresh in Power BI

[pay_2019]

I have a linked excel document held in my Access database which I use to create Power BI reports. The Access database also pulls in data on other ODBC links with third party software. When I refresh the data on desktop it sometimes has issues stating the file may already be in use, or that it cannot refresh the other tables, and I cannot get the schedule refresh to work either. I have checked the file is not in use at the time. After a few manual refreshes on desktop it will work, but not on the initial refresh.

Can you confirm what share settings I should have on the excel document to stop this error occurring, as I am currently manually updating through desktop and then publishing it as a new report each time, which is not ideal?

[pay_2019 · Accepted solution]

I have resolved it by using the table in Access and creating a schedule update to make the table using AutoSQL. Not ideal but it now allows me to set the schedule refresh up and is working.

[Anonymous]

Hi @pay_2019 ,

What is your connection method? Can you refresh the dataset on the Power BI Service on demand? If not, what is the reason for the error? Can you please try to download the pbix file and open it on the Power BI Desktop.

If it is possible, please provide more screenshots with the refresh errors.

Please refer to the topic to see if it helps you.

Refresh excel power pivot linked to SSAS on premise 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pay_2019]

See below the error message if I try to refresh the dataset in place:

Underlying error code: -2147467259
Underlying error message: The key didn't match any rows in the table.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Key: [Schema = "", Item = "Standing cost report"]
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Microsoft.Data.Mashup.ValueError.Table: #table({"Name", "Data", "Schema", "Item", "Kind"}, {})
Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
Activity ID: d2f5945e-3e55-4843-8b93-d24628148eb2
Request ID: 91284bcd-ed30-5baa-c8da-d6f445e41e41
Time: 2022-05-10 08:30:46Z

It does not relate to the Access table listed above called "Standing cost report" as the below error can also happen, "VOR Summary" uses a linked excel table in the query in Access which I think is causing the issue.

Underlying error code: -2147467259
Underlying error message: The key didn't match any rows in the table.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Key: [Schema = "", Item = "VOR Summary"]
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Microsoft.Data.Mashup.ValueError.Table: #table({"Name", "Data", "Schema", "Item", "Kind"}, {})
Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
Activity ID: d2f5945e-3e55-4843-8b93-d24628148eb2
Request ID: 6d0f82b1-3aa2-2326-bfd8-504746ac2ff7
Time: 2022-05-10 08:32:29Z

In some cases on Power BI Desktop the refresh will work without any issue, so unsure why this could be the case? I am wondering whether it would be better to build the queries directly in Power Query within Power BI and link in the excel document for this.

Any suggestions would be appreciated.

Thanks

Paul.