VBA调用SQL存储过程报错：Command text was not set for the command object

Let's break down what's causing your error and fix the code step by step:

Key Issues in Your Code #

• Missing command text for Recordset: When you call rs.Open, you don't provide any query or command to execute. You already set up a Command object for your stored procedure, but you didn't link it to your Recordset.
• Incorrect CommandText syntax: ["ukrmc.dbo.FridayCommentary"] uses array brackets instead of double quotes for a string. This doesn't set the command text correctly.
• Premature connection closure: You close the connection (Conn.Close) right after opening the Recordset, but the Recordset needs an active connection to retrieve data (unless using disconnected cursors).
• Missing Recordset in CopyFromRecordset: Your CopyFromRecordset call doesn't specify which Recordset to copy from (it needs rs as an argument).
• Redundant Recordset setup: You don't need to initialize a separate Recordset and call rs.Open—you can directly get the Recordset from the Command.Execute method.

Corrected VBA Code #

Sub connection()
    Dim Conn As ADODB.Connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    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 & ";"
    
    ' Initialize and open connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = constring
    ' Uncomment error handling if needed
    'On Error GoTo ConnectionError
    Conn.Open
    
    ' Set up command for stored procedure
    Set ADODBCmd = New ADODB.Command
    With ADODBCmd
        .ActiveConnection = Conn
        .CommandTimeout = 1200
        .CommandText = "ukrmc.dbo.FridayCommentary" ' Fixed: use double quotes instead of array brackets
        .CommandType = adCmdStoredProc ' Use named constant for readability (add ADODB reference if needed)
    End With
    
    ' Execute command and get recordset directly
    Set rs = ADODBCmd.Execute
    
    ' Paste recordset to spreadsheet
    ThisWorkbook.Worksheets("macrotest").Range("a2").CopyFromRecordset rs ' Fixed: specify the Recordset
    
    ' Cleanup: Close recordset first, then connection
    rs.Close
    Conn.Close
    
    ' Release objects from memory
    Set rs = Nothing
    Set ADODBCmd = Nothing
    Set Conn = Nothing
    
    Exit Sub ' Exit before error handler if no issues
    
    ' Uncomment error handling if needed
    'ConnectionError:
    'MsgBox "Not possible to log in. Have you entered the correct password?"
    'Cleanup even on error
    'If Not rs Is Nothing Then rs.Close
    'If Not Conn Is Nothing Then Conn.Close
    'Set rs = Nothing
    'Set ADODBCmd = Nothing
    'Set Conn = Nothing
End Sub

Additional Notes #

• Named Constants: Using adCmdStoredProc instead of 4 makes your code more readable. If you get an error for this constant, go to Tools > References in the VBA editor and check "Microsoft ActiveX Data Objects x.x Library".
• Error Handling: I left your commented error handling in place but added cleanup steps to ensure objects are closed even if an error occurs.
• Disconnected Recordsets: If you need to close the connection before working with the Recordset, you can set rs.CursorLocation = adUseClient before executing the command, then set rs.ActiveConnection = Nothing after opening it.

内容的提问来源于stack exchange，提问作者Sorath