sqlexcelvbajoinleft-join

VBA code syntax Error Run Time error '-2147217900(80040e14)' with no comments


Stuck with a bug that I can't beat

runs from

Sub Step2_CreateReportSheetWithConn()
    Dim ws_from$, ws_to$, group_name$
    ws_from = "Data"
    ws_to = "Result"

    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName& ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
    conn.Open

    Dim aggQuery$
    aggQuery = testQ()

    Set rs = CreateObject("ADODB.Recordset")
    ThisWorkbook.Sheets(ws_from).Range("A:B,J:L").NumberFormat = "0" 
    rs.Open aggQuery, conn, 1, 3 ' Run Time error '-2147217900(80040e14)' with no comments    
    If Not rs.EOF Then
        ThisWorkbook.Sheets(ws_to).Cells(6, 1).CopyFromRecordset rs
        rs.Close
    End If
        
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub
Function testQuery()
''' Returns result query from parts combine '''
    
    q_1 = _
        "SELECT [seg], [inn], SUM([kg]) " & _
        "FROM [Данные$] " & _
        "GROUP BY [seg], [inn] "
            
    q_2 = _
        "SELECT [seg], COUNT([inn]), SUM([kg]) " & _
        "FROM [Данные$] " & _
        "GROUP BY [seg] "

    testQuery = _
        "SELECT " & _
            "c.[seg], c.[sku]," & _
       "COUNT(c.[inn]) / COUNT(b.[inn]) as DistrNV, " & _
            "SUM(a.[kg]) / SUM(b.[kg]) as DistrV, " & _
        "FROM [Данные$] AS c " & _
        "LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
        "LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _
        "GROUP BY c.[seg], c.[sku]"
End Function

As I see it, here is the problem place:

        "LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
        "LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _

If I remove 1 line of 2, there will be no error.

I tried swapping keys, swapping "JOIN" rows. Other parts of the request are successfully completed, but there is some kind of trouble at this point.


Solution

  • The syntax for two joins is:

    ([Данные$] AS c LEFT JOIN q_1 AS a ON c.inn=a.inn AND c.inn=a.inn) LEFT JOIN q_2 AS b ON c.seg= b.seg;
    

    Check if parentheses are required.