Excel VBA QueryTable.Commandtext - Select * from [Variable] - (Access Database)

Question

I'm trying to pull Access Queries based on certain input values. Pulling the correct query works, but it gets stuck on the marked position, because it wont accept a variable and just writes it out as it is.

Is there a workaround anybody knows of?

Dim BDMA As String
BDMA = Me.ListBDMA.Value
ActiveWorkbook.Queries.Add Name:=BDMA, Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Quelle = Access.Database(File.Contents(""X:\Pierburg\Berlin\_Common\010 Standort Berlin\300 Datenbanken\Schichtbericht.accdb""), [CreateNavigationProperties=true])," _
    & Chr(13) & "" & Chr(10) & "    #""_Band 3"" = Quelle{[Schema="""",Item=" & BDMA & "]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""_Band 3"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="" & BDMA & "";Extended Properties=""""" _
    , Destination:=Range("$A$5")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & BDMA & "]") '<----- Here is the Problem ----------
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = False
    .ListObject.DisplayName = "Band_3"
    .Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Greetings

Error says:

The query '& BDMA &' wasn't found.

That indicates i can't use variables like that. Any ideas? I also tried creating an array or string beforehand and using that, but that also didn't work.

This line is wrong: "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="" & BDMA & "";Extended Properties=""""" as there should only be one quotation mark: Location=" & BDMA & "; — Rory
– Rory, Commented Sep 23, 2024 at 13:49
... and the & "";Extended Properties=""""" part can be dropped entirely, So, "OLEDB;Provider=Microsoft...;Data Source=$Wo...;Location=" & BDMA. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Sep 23, 2024 at 14:10

Joel Coehoorn · Accepted Answer · 2024-09-23 13:46:12Z

0

Here is the problem

 .CommandText = Array("SELECT * FROM [" & BDMA & "]")

Indeed. The Array() function here is not needed or helpful. It means the code will try to assign a character array to the CommandText property instead of a string, and you want a string. So it should just look like this:

 .CommandText = "SELECT * FROM [" & BDMA & "]"

There may be other issues as well, but start here.

answered Sep 23, 2024 at 13:46

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rory Over a year ago

Commandtext will accept an array just fine.

Olivier Jacot-Descombes Over a year ago

VBA tries hard to convert values to the required type, therefore an Array might work, but it does not make any sense here.

Sally BRB Over a year ago

Sadly that didn't do the trick. I also tried to predefine a string but that didn't work either. It still just uses the "& BDMA &" as it is. When i stop the code beforehand tho and check values, it correctly displays the string.

Olivier Jacot-Descombes Over a year ago

How does the resulting connection string look exactly?

Olivier Jacot-Descombes · Accepted Answer · 2024-09-25 09:56:38Z

There are two problems:

Let's make a little test:

Public Sub TestBDMA()
    Dim BDMA As String, Source As String

    BDMA = "bdma"

    Source = _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="" & BDMA & "";Extended Properties="""""
    Debug.Print "Source = " & Source
End Sub

Prints:

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

Clearly, this is not what it should print. There are double quotes that do not belong there and the variable BDMA was not expanded. Also, if there are no Extended Properties, there is no point in specifying them. Empty parts of a connection string can usually be dropped, and the semicolon at the end also.

So, let's fix the Source assignment:

    Source = _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & BDMA
    Debug.Print "Source = " & Source

Prints:

Source = OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=bdma

Assign a String to CommandText, not an Array.

.CommandText = "SELECT * FROM [" & BDMA & "]" '<--- Problem fixed -----

Collectives™ on Stack Overflow

Excel VBA QueryTable.Commandtext - Select * from [Variable] - (Access Database)

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related