RT 91 on ADO code

Why would I be getting a RT 91 on the last line of this code?

Dim rstUsers As ADODB.Recordset
If FlexGrid.Row <> -1 Then
   FlexGrid.Col = 0
End If

If FlexGrid.Text <> "" Then
    strQuery = "SELECT * From fan_" & cboLoadDB.Text & " Where ID= " & FlexGrid.Text
   rstUsers.open strQuery, Myconn, adOpenStatic, adLockOptimistic
Who is Participating?
inthedarkConnect With a Mentor Commented:
To solve this you can:


set rstUsers = New ADODB.Recordset
rstUsers.open etc....


Note the use of the New  keyword. Also note you cannot always use new, for example if you wanter to trap events for the recordset.  

Dim rstUsers As New ADODB.Recordset

You may also get the same error if the connection is not open but I am not certain of this..

But there is a much better way I will post some code for you, it will save you hours in the future....
Are you displaying headers in first row? If yes then in that case use the following:
If FlexGrid.Row <> 0 Then
   FlexGrid.Col = 0
End If
I have created a handy class for ado operations I declare the class at module level like this

DIM ADO as New zADO  ' note zADO is what I called my class

So you can write code like this:
Private MySub()

Dim MyRS As ADODB.Recordset

DIM SQL As String
SQL= "Select * etc....."
OK = ADO.OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)
If Not Ok Then
    MsgBox  ADO.ErrD, vbExclamation, "My Function Description"
    Exit Sub
End If

' Don't forget to Add projet reference to MS ActiveX Data Objects
Public Function OpenRSOK(CN As adodb.Connection, RS As adodb.Recordset, SQL As String, Optional CursorType As adodb.CursorTypeEnum = adOpenForwardOnly, Optional LockType As LockTypeEnum = adLockReadOnly, Optional CommandType As adodb.CommandTypeEnum = adCmdText, Optional CursorLocation As adodb.CursorLocationEnum = adUseServer, Optional AppendOnly As Boolean = False) As Boolean

' Opens any type of recordset return true is OK

' to open for locking updates
'OK = OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)

' to open for fast readonly
' but don't use client with large tables
'OK = OpenRSOK(CN, RS, SQ, adOpenStatic + adOpenForwardOnly, adLockReadOnly, adCmdText, adUseClient)

' first test to make sure connection is open
If Not IsConnected(CN) Then
    ErrD = "Connection not open or not instanciated"
    ErrN = -1
    Exit Function
End If

' Create a new recordset
Set RS = New adodb.Recordset
If Not IDE Then
    On Error Resume Next
End If


RS.CursorLocation = CursorLocation
If AppendOnly Then
    ' **** Warning only works with Access
    ' but even then it don't work.
    ' so it is best to use some sql a bit like this
    ' where there will never be a record zero
    ' SQL = "Select * from [myTable] Where [MyID]=0
    ' and open the table for normal updates as AppendOnly does not seem to work
    ' as it says in the manual, but it works fine in DAO (somebody ask Bill why?)
    RS.Properties("Append-Only Rowset") = True
End If
RS.Open SQL, CN, CursorType, LockType, CommandType

If Err.Number <> 0 Then
    Set RS = Nothing
    OpenRSOK = False
    OpenRSOK = True
End If

End Function

Public Function IsConnected(CN As adodb.Connection) As Boolean
If Not CN Is Nothing Then
    If CN.State = adStateOpen Then
        IsConnected = True
    End If
End If
End Function
Public Function IDE(Optional Genuine As Boolean = False) As Boolean

' Returns True if running in debug (IDE) mode
'         False if running in an EXE


' Set LiveMode = True in the declarations to override so test app as EXE


' If ADO.IDE Then
'   Stop
' End If

' Also when you realy want to know if in IDE before using function that
' cause IDE to hang
' If Not IDE(True) Then
'   Application.Lock ' Will cuase IDE to hang and need to reboot machine
'   Application("HitCount") = Application("HitCount") + 1
'   Application.UnLock
'   Application("HitCount") = Application("HitCount") + 1
' End If

If Not Genuine And LiveMode Then
    IDE = False
    Exit Function
End If

Static mIDEDone As Boolean
Static mIDE As Boolean

If Not mIDEDone Then ' See below
    ' just do this first time round then store the result
    On Error Resume Next
    Debug.Print 1 / 0; ' THis will cuase error in IDE but is ignored in EXE
    If Err.Number <> 0 Then
        mIDE = True
        mIDE = False
    End If
    mIDEDone = True
End If
End Function

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.