错误：在 VBA 上从 SQL 运行存储过程时"Command text was not set for the command object"

Question

我正在尝试使用以下 VBA 代码在 VBA 上运行存储过程：请有人建议：我在"rs.打开"。

Sub connection()
   Dim Conn As ADODB.connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim constring As String
    Dim location As String  'the server
    Dim password As String

location = "10.103.98.18" 
password = "password"
constring = "Provider=SQLOLEDB; Network Library=DBMSSOCN;Data Source=" & location & ";Command Timeout=0;Connection Timeout=0;Packet Size=4096; Initial Catalog=ElColibri; User ID=Analyst1; Password=password;"

Set Conn = New ADODB.connection
Conn.connectionString = constring
'On Error GoTo ConnectionError
Conn.Open



'loginstatus = False
'Exit Sub

'errorhandl0
'ConnectionError:
'MsgBox "Not possible to log in. Have you entered the correct password?"
'open recordset
Set ADODBCmd = New ADODB.Command
ADODBCmd.ActiveConnection = Conn
ADODBCmd.CommandTimeout = 1200
ADODBCmd.CommandText = ["ukrmc.dbo.FridayCommentary"]
ADODBCmd.CommandType = 4 'adCmdStoredProc
ADODBCmd.Execute
Set rs = New ADODB.Recordset
rs.ActiveConnection = Conn
rs.Open
Conn.Close
Set Conn = Nothing
Set ADODBCmd = Nothing
'Paste to spreadsheet
ThisWorkbook.Worksheets("macrotest").Range("a2").CopyFromRecordset
'Set rs = conn.Execute(Query)
rs.Close
Set rs = Nothing

End Sub

对我来说，

代码对我来说是合乎逻辑的，所以我不确定错误意味着什么。因为对我来说，我已经为命令对象设置了文本。

Answer 1

您没有将记录集连接到您的命令。假设您的存储过程发出SELECT ，请将您的代码更改为

Set rs = ADODBCmd.Execute
thisWorkbook.Worksheets("macrotest").Range("a2").CopyFromRecordset rs

结果，Execute -Method 将返回一个Recordset，无需自行创建。

Answer 2

或者

您应该将命令对象添加到打开的操作中

rs.Open ADODBCmd