VBA Error while running Stored Proc - run time error 91 object variable or with block not set

Question

I am new to VBA and trying to call a sybase ASE stored proc and get the result set back, this is what i am doing. While running i am getting error "run time error 91 object variable or with block not set" at line opening the connection (dbCon.Open connectionString), can anyone please provide some pointers ?

Sub GetEmployees()
    Dim dbCon As ADODB.Connection
    Dim rstTemp As New ADODB.Recordset
    Dim query As String
    Dim connectionString As String
    query = "exec sp"
    connectionString = "Driver=Sybase.ASEOLEDBProvider;Server Name=serverName,port;
    Initial Catalog=DBname;User id=user;Password=pass;"
    dbCon.Open connectionString
    dbCon.CommandTimeout = 600
    rstTemp.Open query, dbCon, adOpenForwardOnly
End Sub

silentsurfer silentsurfer · Accepted Answer · 2015-01-06T17:41:14

You need to instantiate your Connection object.

Use either:

Dim dbCon As New ADODB.Connection

or

Dim dbCon As ADODB.Connection
Set dbCon = New ADODB.Connection

VBA Error while running Stored Proc - run time error 91 object variable or with block not set

1 Answers