vba - 尝试使用 OLEDBConnection 运行时错误 '1004' : Application-defined or object-defined error 运行查询

Question

我在 .Connection 上收到 1004 错误

    With ActiveWorkbook.Connections(ConnectionName).OLEDBConnection
    .BackgroundQuery = True
    .CommandText = MyCommandText
    .CommandType = xlCmdSql
    .Connection = ConnectionString

ConnectionString 看起来像这样:

"Provider=SQLOLEDB.1" & Chr(59) & "Integrated Security=SSPI" & Chr(59) & _
"Persist Security Info=True" & Chr(59) & "Initial Catalog=wss_back" & Chr(59) & _
"Data Source=XX-X-0009999.de.xxx.com,12345" & Chr(59) & _
"Use Procedure for Prepare=1" & Chr(59) & "Auto Translate=True" & Chr(59) & _
"Packet Size=4096" & Chr(59) & "Workstation ID=XX-X-1234567" & Chr(59) & _
"Use Encryption for Data=False" & Chr(59) & _
"Tag with column collation when possible=False"

知道为什么会失败吗？

Answer 1

问题在于:

    .CommandType = xlCmdSql

CommandType 需要来自 ADODB.CommandTypeEnum 的值，但您提供的值来自 xlCmdType。 xlCmdSql = 2，这与 ADODB.adCommandTypeEnum.adCmdTable 相同，而不是 adCmdText，这是您在这里想要的。

所以改成:

With ActiveWorkbook.Connections(ConnectionName).OLEDBConnection
  .BackgroundQuery = True
  .CommandText = MyCommandText
  .CommandType = adCmdText
  .Connection = ConnectionString

...它应该可以工作。