Syntax error (missing operator) in query expression VBA while multiple inner join

1
votes

I am getting below Syntax error (missing operator)

enter image description here

Please find my below VBA code.

Sub macro()

sql_string = "SELECT [Sheet2$].[Sr], [no], [Code], [Sheet3$].[Srr], [Family], [nos], [Sheet1$].[Sr], [LongName]" & _
" FROM [Sheet3$], [Sheet2$], [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet2$].[Sr]=[Sheet3$].[Srr]" & _
" INNER JOIN [Sheet2$] ON [Sheet2$].[no]=[Sheet3$].[nos]"

sq = SQL_query(sql_string)

end sub

Function SQL_query(ByRef sql_string As Variant)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = sql_string

rs.Open strSQL, cn

Sheet5.Range("A21").CopyFromRecordset rs
End Function

Please find the below tables.

Table1-

enter image description here

Table2-

enter image description here

Table3-

enter image description here

Please guide where i am missing in the code.

2
sqlvbaexceladodb
which line does the code break on? Is it rs.Open strSQL, cn ? – ashleedawg
@ashleedawg Yes Sir.. – sagar
In Access, ACE, I believe you can only join two tables at a time. I've tried before and ended up frustrated (SQL Server, this would work). You'd have to restructure it like this answer, here – Jimmy Smith
You can join multiple tables, I don't think that's the issue – ashleedawg

2 Answers

2
votes

Thank all for the help, As per your guidance.

the below syntax are working for me for multiple inner join.

sql_string = "SELECT [Sheet2$].[Sr], [no], [Code], [Sheet3$]." & _
    "[nos], [Family], [Sheet1$].[LongName]" & _
    " FROM (([Sheet2$] INNER JOIN [Sheet3$] ON [Sheet2$].[Sr]=[Sheet3$].[Srr])" & _
    " INNER JOIN [Sheet1$] ON [Sheet1$].[Sr]=[Sheet3$].[Srr])"
1
votes

A good way to troubleshoot messy SQL queries in VBA is to break them up, since SQL will ignore all the extra spaces & line breaks but we will be able to better understand it, and find problems.

So, we could split your line with the query up like:

sql_string = "
    SELECT
        [Sheet2$].[Sr],
        [no],
        [Code], 
        [Sheet3$].[Srr], 
        [Family], 
        [nos], 
        [Sheet1$].[Sr], 
        [LongName]
    FROM [Sheet3$], [Sheet2$], [Sheet1$] 
    INNER JOIN [Sheet2$] 
        ON [Sheet2$].[Sr]=[Sheet3$].[Srr]
    INNER JOIN [Sheet2$] 
        ON [Sheet2$].[no]=[Sheet3$].[nos]"

and now we can see a few mix-ups in your joins, which I further tidied up below, as well as specified table names on all the field. There's a couple issues, for example You can't join 3 tables to 1 table, but you can do a join between two tables, and then a join between "that" and the third table.

sql_string = "
    SELECT
        [Sheet2$].[Sr],
        [Sheet2$].[no],
        [Sheet2$].[Code], 

        [Sheet3$].[Srr], 
        [Sheet3$].[nos], 
        [Sheet3$].[Family], 


        [Sheet1$].[Sr], 
        [Sheet1$].[LongName]

    FROM [Sheet2$]
    INNER JOIN [Sheet3$]
        ON [Sheet2$].[Sr]=[Sheet3$].[Srr]

    INNER JOIN [Sheet1$] 
        ON [Sheet1$].[Sr]=[Sheet3$].[Srr]"

Paste that in to replace your sql_string = "SELECT [Sheet2$].... line (as is, ignore how long it is now)

Let me know how it goes!

Here's a link to more info on nested Inner Joins from Microsoft Support

You can also link several ON clauses in a JOIN statement, using the following syntax:

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1compoprtable2.field1 AND
ON table1.field2compoprtable2.field2) OR
ON table1.field3compoprtable2.field3)];

You can also nest JOIN statements using the following syntax:

SELECT fields FROM table1 INNER JOIN (table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)] ON
table3.field3compoprtablex.fieldx)] ON
table2.field2compoprtable3.field3) ON
 table1.field1compoprtable2.field2;

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN. (That last part shouldn't apply in this case since you've got straight nested INNER JOINs now.)