错误[42000] [Microsoft] [ODBC Excel驱动程序]语法错误（缺少操作员）

发布于 2025-02-08 11:51:11 字数 872 浏览 3 评论 0原文

尝试使用ODBC驱动程序访问Excel文件作为数据库。并且需要使用以下查询在Excel WB中加入3个表（表1,2,3）：

"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

在加入3表时我会发现此错误，如上所示：

运行查询：错误[42000] [Microsoft] [ODBC Excel驱动程序]语法错误（缺少操作员）在查询表达式'[Sheet1 $]中。glaccnum = [Sheet2 $]。glaccnum placcnum weft [sheet3 $] on [sheet3 $] on [sheet3 $] $]。glaccnum = [Sheet1 $]。glaccnu'。

但是当我只加入2张桌子时，没关系！

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

熟人话多 2025-02-15 11:51:11

在Excel ODBC连接使用的MS Access SQL方言中，多个连接需要括号。希望MS团队会注意我的

因此，在括号中上的之后，将第一个从join对包装。

"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

还考虑一种替换方法，以使您的SQL可读性。下面用使用表别名并显示了两个括号对，也可以解决您的语法问题。

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

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

回复收藏 0 原文

~没有更多了~