Syntax Error on SQL Query using LEFT OUTER JOIN

Question

I am trying to do the following process, I have two distinct access databases, and I am programming some sql queries into Excel Vba to manipulate the data of these databases. I did the link with these database using ADOX Connection, you could see the script below. My question is related to the query itself, from one table I have a lot of data, and I am looking these data from another table, that are on another DB, but if the query do not find a relation it has to inform all the data from the first table, and use the other tables as complementary information, If there is no match then the result will be null.

To do such thing I am using the LEFT OUTER JOIN on all tables, but it do not fit. As it returns the message

"Syntax Error on JOIN operation"

How can I fix this error?

Public Function getUMEByIdent()
Dim i As Long
Dim j As Integer
Dim nData() As Variant
Dim adox_table1 As ADOX.Table
Dim adox_table2 As ADOX.Table
Dim adox_table3 As ADOX.Table
Dim fatorConv As Double

With connection
    .ConectDB ("compDraw.mdb")
        Set adox_catalog = New ADOX.Catalog
        Set adox_catalog.ActiveConnection = connection.conn
        
        Set adox_table1 = New ADOX.Table
        With adox_table1
            Set .ParentCatalog = adox_catalog
            .Name = "lkdTbl1"
            .Properties("Jet OLEDB:Link Datasource") = ThisWorkbook.Path & "\assets\configs.mdb"
            .Properties("Jet OLEDB:Link Provider String") = "MS Access"
            .Properties("Jet OLEDB:Remote Table Name") = "mditems"
            .Properties("Jet OLEDB:Create Link") = True
        End With
    
        Set adox_table2 = New ADOX.Table
        With adox_table2
            Set .ParentCatalog = adox_catalog
            .Name = "lkdTbl2"
            .Properties("Jet OLEDB:Link Datasource") = ThisWorkbook.Path & "\assets\configs.mdb"
            .Properties("Jet OLEDB:Link Provider String") = "MS Access"
            .Properties("Jet OLEDB:Remote Table Name") = "umestatistics"
            .Properties("Jet OLEDB:Create Link") = True
        End With

        Set adox_table3 = New ADOX.Table
        With adox_table3
            Set .ParentCatalog = adox_catalog
            .Name = "lkdTbl3"
            .Properties("Jet OLEDB:Link Datasource") = ThisWorkbook.Path & "\assets\configs.mdb"
            .Properties("Jet OLEDB:Link Provider String") = "MS Access"
            .Properties("Jet OLEDB:Remote Table Name") = "convume"
            .Properties("Jet OLEDB:Create Link") = True
        End With
        
        Set .rs = .conn.Execute( _
            "SELECT bom.ident_mp, lkdTbl1.ncm, lkdTbl1.umc, lkdTbl2.ume, lkdTbl3.fator_conv FROM" & _
            " ((bom LEFT OUTER JOIN lkdTbl1 ON lkdTbl1.ident = bom.ident_mp) " & _
            " LEFT OUTER JOIN tkdTbl2 ON lkdTb1.ncm = lkdTbl2.ncm)" & _
            " LEFT OUTER JOIN lkdTbl3 ON lkdTbl1.umc = lkdTbl3.umc AND lkdTbl2.ume = lkdTbl3.ume" & _
            " GROUP BY bom.ident_mp, lkdTbl1.ncm, lkdTbl1.umc, lkdTbl2.ume, lkdTbl3.fator_conv", , adCmdText)
    
        Do While Not .rs.EOF
            If IsNull(.rs.Fields("ident_mp").Value) = False Then
                ReDim Preserve nData(i)
                nData(i) = Array(.rs.Fields("ident_mp").Value, .rs.Fields("ncm").Value, .rs.Fields("umc").Value, .rs.Fields("ume").Value, .rs.Fields("fator_conv").Value)
                i = i + 1
            End If
            
            .rs.MoveNext
        Loop
        .rs.Close
    
        adox_catalog.Tables.Delete "lkdTbl1"
        adox_catalog.Tables.Delete "lkdTbl2"
        adox_catalog.Tables.Delete "lkdTbl3"
    .FechaDb
End With
End Function

dkusleika_4280 · Answer

Access' query parser can't handle certain joins. You can usually work around it by creating a subquery.

SELECT   temp.ident_mp
       , temp.ncm
       , temp.umc
       , temp.ume
       , lkdTbl3.fator_conv
FROM
         (SELECT *
          FROM   bom
                 LEFT OUTER JOIN lkdTbl1 ON
                     lkdTbl1.ident = bom.ident_mp
                 LEFT OUTER JOIN tkdTbl2 ON
                     lkdTb1.ncm = lkdTbl2.ncm) temp
         LEFT OUTER JOIN lkdTbl3 ON
             temp.umc = lkdTbl3.umc
             AND temp.ume = lkdTbl3.ume
GROUP BY temp.ident_mp
       , temp.ncm
       , temp.umc
       , temp.ume
       , lkdTbl3.fator_conv

This puts the first two left joins into a subquery named temp so that the last left join is only joining two tables. I couldn't test it because I don't have your setup, but I did something similar in Northwind.mdb and it worked.

Syntax Error on SQL Query using LEFT OUTER JOIN

1 Answers