FROM子句Excel VBA中的语法错误

本文介绍了FROM子句Excel VBA中的语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我的代码，用于从访问中检索数据，但是我经常遇到运行时错误'-2147217900(80040e14)'FROM子句中的语法错误"

Here is my code to retrieve data from access but I always encounter "Run-time error '-2147217900 (80040e14)' Syntax error in FROM clause"

Sub UPDATE_REGION() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim AW As Workbook Set AW = ActiveWorkbook Path = AW.Path cnn_pth = Path & "\Master File.accdb" Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.ACE.OLEDB.12.0" .Open cnn_pth End With Set rst = New ADODB.Recordset sSQL = "select Package_Nb from [package_db] where [Hubs] is null" rst.Open Source:=sSQL, ActiveConnection:=cnn, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdTable end subs

当我停止调试脚本时，我在本地窗口中找到了源值，如下所示:"select * from select * from package_db中的Package_Nb，其中Hubs为空"

When i stop script to debug, I found Source value in Local Window like this: "select * from select Package_Nb from package_db where Hubs is null"

我不知道为什么vba会这样向源自动添加 select * from ，有人知道如何解决此问题吗?

I don't know why vba auto add select * from to source like this, anyone know how to fix this?

推荐答案

您的选项有误.这个:

Options:=adCmdTable

应该是这样:

Options:=adCmdText

由于您没有传递表名，而是传递了SQL字符串.(当您传递表名时，它会有效地转换为 SELECT * FROM table_name 语句).

Since you are not passing a table name but a SQL string. (when you pass a table name it is effectively converted into a SELECT * FROM table_name statement).