.Refresh BackgroundQuery:=False doesn't always work

Question

I am quite new to VBA* and I would like to have your help with the following problem: I use the following code and for the two first cases (well, first two IFs) the code works perfectly well, I get the tables from Access imported into my Excel file. However, the code doesn't work in case of the two other IFs (EDIP 2003 V1.06 / Default and EPD (2013) V1.04). Whenever I try to use one of the two to import the corresponding table, Excel states (error 1004): Initialization data source failed

Excel highlights the line

.Refresh BackgroundQuery:=False

*I am an engineer architect and I used VBA to develop an Excel-based calculation tool for environmental impacts of hospital buildings for my PhD thesis. So, I am not at all that familiar with VBA codes!

Here is the code:

  Sub MacroTest()
'
' MacroTest Macro
'

'

If Worksheets("General").Range("C4").Value = "AWARE" Then
    ActiveWorkbook.Queries.Add Name:="DB_AWARE_impact_indicators", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Access.Database(File.Contents(""C:\Users\mbruyninckx\Dropbox\LCA-BREEAM tool\LCA-BREEAM\__VK MAT 01 LCA tool\_Database\Access database\Database_LCA_tool.accdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DB_AWARE_impact_indicators = Source{[Schema="""",Item=""DB_AWARE_impact_indicators""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _DB_AWARE_impact_indicators"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_AWARE_impact_indicators"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [DB_AWARE_impact_indicators]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "DB_AWARE_impact_indicators"
        .Refresh BackgroundQuery:=False
        ActiveSheet.Name = "DB_AWARE_impact_indicators"
    End With

ElseIf Worksheets("General").Range("C4").Value = "CED (Cumulative Energy Demand)" Then
        ActiveWorkbook.Queries.Add Name:="DB_CED_impact_indicators", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Access.Database(File.Contents(""C:\Users\mbruyninckx\Dropbox\LCA-BREEAM tool\LCA-BREEAM\__VK MAT 01 LCA tool\_Database\Access database\Database_LCA_tool.accdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DB_CED_impact_indicators = Source{[Schema="""",Item=""DB_CED_impact_indicators""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _DB_CED_impact_indicators"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_CED_impact_indicators"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [DB_CED_impact_indicators]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "DB_CED_impact_indicators"
        .Refresh BackgroundQuery:=False
        ActiveSheet.Name = "DB_CED_impact_indicators"
    End With

ElseIf Worksheets("General").Range("C4").Value = "EDIP 2003 V1.06 / Default" Then
        ActiveWorkbook.Queries.Add Name:="DB_EDIP_2003_impact_indicators", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Access.Database(File.Contents(""C:\Users\mbruyninckx\Dropbox\LCA-BREEAM tool\LCA-BREEAM\__VK MAT 01 LCA tool\_Database\Access database\Database_LCA_tool.accdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DB_EDIP_2003_impact_indicators = Source{[Schema="""",Item=""DB_EDIP_2003_impact_indicators""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _DB_EDIP_2003_impact_indicators"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_EDIP_2003_impact_indicators""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [DB_EDIP_2003_impact_indicators]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "DB_EDIP_2003_impact_indicators"
        .Refresh BackgroundQuery:=False
        ActiveSheet.Name = "DB_EDIP_2003_impact_indicators"
    End With

ElseIf Worksheets("General").Range("C4").Value = "EPD (2013) V1.04" Then
        ActiveWorkbook.Queries.Add Name:="DB_EPD_2013_V1_04_impact_indicators", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Access.Database(File.Contents(""C:\Users\mbruyninckx\Dropbox\LCA-BREEAM tool\LCA-BREEAM\__VK MAT 01 LCA tool\_Database\Access database\Database_LCA_tool.accdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DB_EPD_2013_V1_04_impact_indicators = Source{[Schema="""",Item=""DB_EPD_2013_V1_04_impact_indicators""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _DB_EPD_2013_V1_04_impact_indicators"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_EPD_2013_V1_04_impact_indicators""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [DB_EPD_2013_V1_04_impact_indicators]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "DB_EPD_2013_V1_04_impact_indicators"
        .Refresh BackgroundQuery:=False
        ActiveSheet.Name = "DB_EPD_2013_impact_indicators"
    End With

End If
End Sub

Its difficult to tell, but maybe you have a problem in your Power Query code. Try to record the two macros outputing the two queries again, check that both of them work and replace the code between the IFs that are failing — Ricardo Diaz
Thanks, it was not that. I have just solved the problem, see below. :) — mbruyninckx

mbruyninckx mbruyninckx · Accepted Answer · 2020-01-17T09:21:50

I've solved the issue. The problem is in the line:

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_EDIP_2003_impact_indicators""""" _

It should be

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DB_EDIP_impact_indicators"";Extended Properties=""""" _

.Refresh BackgroundQuery:=False doesn't always work

1 Answers