CODEWITHSUNDEEP
excelms-accessvba
yuro
yuro

Reputation: 2209

Runtime error 91 in VBA excel for ADODB.Recordset

I'm getting the runtime error 91 after I try a to send a sql statement in my excel application. Here's the following code:

'Module Consts:
Public rst1 As New ADODB.Recordset 'defined in a constants module
'Module Conn:
Public Sub GetDynSQLQuery(ByVal strQuery$, ByVal rs As ADODB.Recordset)   
    Set rs = Nothing
    If cn = "" Then Call ConnectToDb 'Sub set the variable "cn" with connectionstring to db and open the connection

    With rs
      .Source = strQuery       'Here comes the error
      .ActiveConnection = cn
      .CursorType = adOpenForwardOnly
      .LockType = adLockReadOnly
      .Open
    End With
End Sub

'Form1:
strSql = "SELECT * FROM tbl1"
Call GetDynSQLQuery(strSql, rst1)

Error Message:

Object variable or With-block variable not set

What I'm doing wrong I cannot see it.

Upvotes: 1

Views: 2121

Answers (1)

R3uK
R3uK

Reputation: 14537

The first thing you do in the sub is that you empty the Argument (with Set rs = Nothing) that you just passed and then you use rs again.

So except if rs is a Public-like variable and it's filled in ConnectToDb,
it remains empty before being used, ergo the error!

First, try to remove Set rs = Nothing and if it's not enough, you'll need to look into ConnectToDb!

Secondly, you modify the Recordset inside the sub, but you try it to use it outside.
And the problem here was the use of ByVal, which pass a copy of the reference of the Object and so you canNOT modify the initial object, ergo the fact that the RecordSet is empty outside of the Sub! ;)

And as OP tested rst1.RecordCount > 0 which was false (rst1.RecordCount was equal to -1),
He fixed it by setting .CursorType to adOpenKeySet instead of adOpenForwardOnly

Public Sub GetDynSQLQuery(ByVal strQuery$, rs As ADODB.Recordset)   
    'Sub set the variable "cn" with connectionstring to db and open the connection
    If cn = vbNullString Then Call ConnectToDb

    With rs
      .Source = strQuery       'No more error!^^
      .ActiveConnection = cn
      .CursorType = adOpenKeySet
      .LockType = adLockReadOnly
      .Open
    End With
End Sub

Upvotes: 2

Related Questions