RT 91 on ADO code

Posted on 2007-07-30
Last Modified: 2010-04-30
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 strQuery, Myconn, adOpenStatic, adLockOptimistic
Question by:Takamine334
    LVL 18

    Expert Comment

    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
    LVL 17

    Accepted Solution

    To solve this you can:


    set rstUsers = New ADODB.Recordset 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....
    LVL 17

    Expert Comment

    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 OK
    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
    IDE = mIDE
    End Function


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now