FROM子句Excel VBA中的语法错误 [英] Syntax error in FROM clause 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).

这篇关于FROM子句Excel VBA中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！