Can’t do Multiple Left Joins SQL Select (ODBC)

When I run the following SQL select statement with two left joins:

SELECT t01.owner FROM rnglib001.gldtpf t01 LEFT JOIN rngmst001.wlddpf t02 ON t01.prprty = 
t02.prprty LEFT JOIN rngmst001.glmtpf t03 ON t01.gen = t03.gen AND t01.sub = t03.sub WHERE 
t01.gen = 120 AND t01.sub = 17 AND t01.acctmo = 12 AND t01.acctyr = 2021 AND t01.jenum IN 
(363, 981)

I get the following error message in Excel VBA:

“Run-time error ‘5’: Invalid procedure call or argument”

When I run that exact same SQL without the left joins, it works without issue.

SELECT t01.owner FROM rnglib001.gldtpf t01 WHERE t01.gen = 120 AND t01.sub = 17 AND t01.acctmo 
= 12 AND t01.acctyr = 2021 AND t01.jenum IN (363, 981)

I know the first SQL statement above is valid because it works just fine (joins-included) when I import it through ODBC in the Excel “get data from other sources” tool:

Do you know what I am doing wrong here? My goal is to run the original Select statement in VBA and have the results populated in a table object. I have done this before countless times but I guess I have not done it with two left joins in the SQL.

Just for grins, here is my code:

#################################################################################

    Sub importKDGLARJIBS()
    
    '================================================================================
    'Check if sheet is blank or not.
    'If blank, run code. If not, create new sheet first then run.
    '================================================================================
        'Checks if the sheet is blank
        If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 _
            And ActiveSheet.Shapes.Count = 0 Then
        'If sheet is empty, proceed with macro
        Else
        'If not blank, create new sheet
            Sheets.Add
        End If
    
    '================================================================================
    'Declare variables
    '================================================================================
        Dim Mydt As String
        Dim myLib As String
        Dim myFile As String
    
        'Assign user-input to variable
        Mydt = Format(TimeValue(Now), "hhnnss")
        
    '================================================================================
    'Create select statement
    '================================================================================
        'Build Select statement
        mySel = "SELECT t01.owner FROM rnglib001.gldtpf t01 LEFT JOIN rngmst001.wlddpf t02 ON t01.prprty = t02.prprty LEFT JOIN rngmst001.glmtpf t03 ON t01.gen = t03.gen AND t01.sub = t03.sub WHERE t01.gen = 120 AND t01.sub = 17 AND t01.acctmo = 12 AND t01.acctyr = 2021 AND t01.jenum IN (363, 981)"
        
        'Send Select statement / build QueryTable object with output
        On Error GoTo ErrHandler
        With ActiveSheet.ListObjects.Add(SourceType:=0, _
            Source:="ODBC;DSN=AS400 Production;", _
            Destination:=Range("$A$1")).QueryTable
            .CommandText = Array(mySel) 'Sending Select statement here
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Tbl_Qry_" & Mydt 'Naming the table here
            .Refresh BackgroundQuery:=False
            .Delete
        End With
    
    Exit Sub
    
    '================================================================================
    'Error handling
    '================================================================================
    'If error during SQL portion of module:
ErrHandler:
        MsgBox "There was an issue getting data from Horizon." _
            & Chr(13) & "======================================" _
            & Chr(13) & "Error Number: " & Err.Number _
            & Chr(13) & "Error Description: " & Err.Description _
            & Chr(13) & "======================================" _
            & Chr(13) & "Please try again."
        Exit Sub
    
    End Sub

Thank you!

Answer

The answer is very simple.

Per Tim Williams, all I had to do was just remove the Array() from the .CommandText line and that fixed it.

Advertisement

Answer