Formula.Firewall Issue

[mikeymay]

I have an Excel file that uses Power Query to get data from two Access tables.

The query is executed via the following VBA code in the Workbook_Open code

ActiveWorkbook.Connections("Query - SourceFileName").Refresh
ActiveWorkbook.Connections("Query - GoodsOut").Refresh

The SourceFileName query has three steps
1 - = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content] - Where 'tblFilePath' holds the location of the Access database
2 - = Table.TransformColumnTypes(Source,{{"File_Path", type text}})
3 - = #"Changed Type"{0}[File_Path]

This is then referrred to for two other queries to get data from two tables in Access
= Access.Database(File.Contents(SourceFileName&"\StockMovement.accdb"), [CreateNavigationProperties=true])

When I have to replace the current Excel file that holds the above with an updated file, I get the Formula.Firewall message when the query that refers to the SourceFileName tries to execute.

To resolve this I have to go into the Query Settings and change the Privacy level to ignore for the workbook and then save the file.

As the file can be used as read-only, if it is open and someone else opens it, the VBA bugs out on

ActiveWorkbook.Connections("Query - SourceFileName").Refresh
ActiveWorkbook.Connections("Query - GoodsOut").Refresh

It's becoming more of an issue for me as I am building more files that uses PQ and ideally I'd like to implement a fix rather than having to manually set the privacy levels as and when it bugs out.

TIA

 

[mikeymay]

Below is the Advanced Editor code which might make my issue a bit clearer

Getting the location of the data in the Access file

let
    Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File_Path", type text}}),
    Source_Data_File_Path = #"Changed Type"{0}[File_Path]
in
    Source_Data_File_Path

Refreshing the file with the required data in access

let
    Source = Access.Database(File.Contents(SourceFileName&"\StockMovement.accdb"), [CreateNavigationProperties=true]),
    _GoodsIn = Source{[Schema="",Item="GoodsIn"]}[Data]
in
    _GoodsIn