ERROR [42000] [Microsoft][ODBC Excel Driver] Syntax error (missing operator)-CodePudding

Trying to access an Excel file as database using ODBC driver. and need to join 3 tables (Sheets1,2,3) in excel WB using following query :

"select [Sheet1$]."   CompanyCode_ColName   
", [Sheet1$]."   CompanyName_ColName  
", [Sheet1$]."   GLAccountNumber_ColName  
", [Sheet1$]."   GLAccountName_ColName  
", [Sheet1$].EndingBalance1, [Sheet2$].EndingBalance2,[Sheet3$].EndingBalance3 "  
"from [Sheet1$] left join [Sheet2$] "  
"on [Sheet1$]."   GLAccountNumber_ColName  
" = [Sheet2$]."   GLAccountNumber_ColName  
" left join [Sheet3$] "  
"on [Sheet3$]."   GLAccountNumber_ColName  
" = [Sheet1$]."   GLAccountNumber_ColName

I get this error when joining the 3 tables as shown above:

Run query: ERROR [42000] [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '[Sheet1$].GLAccNum = [Sheet2$].GLAccNum left join [Sheet3$] on [Sheet3$].GLAccNum = [Sheet1$].GLAccNu'.

But it's okay when I join just 2 tables!

Thanks

CodePudding user response：

In the MS Access SQL dialect of which the Excel ODBC connection uses, parentheses are required for more than one join. Hopefully, the MS team heeds my feedback ticket (among other items) to change this frustrating syntax requirement on future versions.

Therefore, wrap the first FROM-JOIN pair after ON in parentheses.

"from ([Sheet1$] left join [Sheet2$] "            ' OPENING PARENETHESIS
"on [Sheet1$]."   GLAccountNumber_ColName  
" = [Sheet2$]."   GLAccountNumber_ColName  
") left join [Sheet3$] "                          ' CLOSING PARENETHESIS
"on [Sheet3$]."   GLAccountNumber_ColName  
" = [Sheet1$]."   GLAccountNumber_ColName

Consider also a replace method for readability of your SQL. Below uses table aliases and shows two parentheses pairs which can also resolve your syntax issue.

Dim var As variant
...

strSQL = "SELECT s1.%CompanyCode_ColName%" _ 
       & "     , s1.%CompanyName_ColName%" _
       & "     , s1.%GLAccountNumber_ColName%" _
       & "     , s1.%GLAccountName_ColName%" _
       & "     , s1.%EndingBalance1%" _ 
       & "     , s2.%EndingBalance2%" _
       & "     , s3.%EndingBalance3%" _
       & " FROM (([Sheet1$] s1" _
       & " LEFT JOIN [Sheet2$] s2" _
       & "    ON s1.%GLAccountNumber_ColName% = s2.%GLAccountNumber_ColName%)" _
       & " LEFT JOIN [Sheet3$] s3" _
       & "    ON s3.%GLAccountNumber_ColName% = s1.% GLAccountNumber_ColName%)"

For Each var in Array("CompanyCode_ColName", "CompanyName_ColName", _
                      "GLAccountNumber_ColName", "GLAccountName_ColName", _
                      "EndingBalance1", "EndingBalance2", "EndingBalance3")

    strSQL = Replace(strSQL, "%" & var & "%", var)
Next var