Getting Object Variable, Block Variable not set error trying to use VBA to pull SQL Server stored procedure

Question

I'm attempting to pull a stored procedure from SQL Server using VBA. I'm able to pull basic SQL Code from the server, but when trying to use a complex code with a stored procedure, I'm getting a Error 91: Object Variable or With block variable not set. Haven't been able to determine what I need to set to make this run correctly.

The stored procedure with done without parameters, and do not have code for parameters listed. I've tried some parameter code from other topics in this forum, but no change

Removing the cmd lines and running it just for a SQL Code (SELECT * FROM Table) did work

Sub ConnectSQL()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim sConnString As String
'Create Connection String
    sConnString = "Provider=SQLOLEDB;Data Source=ServerName;" & _
        "Initial Catalog=DatabaseName;" & _
        "User ID=ID;" & _
        "Password=Password;" & _
        "Integrated Security=SSPI;"
'Create Connection and Recordset Objects
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
'Open Connection and Execute
    conn.Open sConnString
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Paint558Ranking"
    Set rs = cmd.Execute
'Check If Data
    If Not rs.EOF Then
'Transfer Result
        Sheets("Sheet1").Range("A1").CopyFromRecordset rs
'Close record
        rs.Close
    Else
        MsgBox "Error: No Records Returned.", vbCritical
    End If
'Clean Up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Sub

@CindyMeister that "you dont set the cmd object" comment would have been a fine upvote-worthy answer btw ;-) — Mathieu Guindon
– Mathieu Guindon, Commented Feb 6, 2019 at 23:54
@MathieuGuindon If I'd known the correct syntax for what to set it to I would have answered :-) Since I was on a mobile device at the time I couldn't research it, but figured the OP or someone else could, once it was clear why the error was occurring. And for me, such a thing is borderline "non-repro" to close... — Cindy Meister
– Cindy Meister, Commented Feb 7, 2019 at 6:05

Mathieu Guindon · Accepted Answer · 2019-02-06 19:19:48Z

2

"Object [or With block] variable not set" is VBA's equivalent of other languages' "null reference exception", an error that occurs when you try to access an object's members when that object's reference is null, or in VBA terms, Nothing.

That's precisely what's happening here:

cmd.ActiveConnection = conn

The cmd object was declared, but never initialized. Set it to a New instance of an ADODB.Command class to fix the problem:

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

The "With block" part of the error message refers to the With block syntax:

Dim cmd As ADODB.Command
With cmd ''<< error 91 here
    '...
End With

You can use With New to have the object reference held by the With block, without needing to declare a local variable:

With New ADODB.Command
    '...
End With

edited Feb 6, 2019 at 19:19

answered Feb 6, 2019 at 19:15

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Object Variable, Block Variable not set error trying to use VBA to pull SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related