Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: cursor type and Row does not support backward fetching problem

    I looked at some other similar posts on this error and see that the Rowset does not support backward fetching is a cursor type error, but I don't know how to correct it in my code, below from an Access 2000 db. One thing I did try was removing Result.MoveLast and instead adding ORDER BY workID DESC to the SQLQuery but then it told me the database was put in a state by an unknown user that prevented it from being opened or locked when I ran it. Need help with this big time!


    Code:
    Public Function GetworkID()
    Dim WO, Result, OBJConnection
    
    Set OBJdbConnection = CreateObject("ADODB.Connection")
    OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=F:\Users\redacted\IT_Database.mdb"
    SQLQuery = "SELECT * FROM work_requests_tbl"
    Set Result = OBJdbConnection.Execute(SQLQuery)
    
    If Not Result.EOF Then
        Do While Not Result.EOF
                Result.MoveLast
                WO = Result("workID")
        Loop
    End If
    OBJConnect.Close
    GetworkID = WO
    End Function
    Last edited by loquin; 01-29-09 at 15:06. Reason: add [code] [/code] tags to improve readability.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    what version of VB are you using?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2009
    Posts
    3
    I probably posted this in the wrong section, but in any event someone on UtterAccess forums solved it for me last night. Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hi HockeyTech71,
    Please can you post the link where you got the answer from so that future readers may benefit?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Here is the link to the resolution of this on UtterAccess Forums. It's about 8 back and forth posts but nevertheless solved by the end. Utter Access Discussion Forums - Rowset does not support backward fetching error

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Your definition of variables

    Code:
    Dim WO, Result, OBJConnection
    creates variant data types, which aggrevates things.
    Code:
    Dim WO as Long  ' Or, whatever data type best matches the field type for workID...
    Dim Result as ADODB.Recordset
    Dim OBJConnection as ADODB.Connection
    would be better. Variants tend to result in code which is less efficient than specifying the actual datatypes at the onset.

    Typically, with VB, the error you're seeing occurs when you accept default recordset parameters for cursor type. With the implicit assignment of:
    Code:
    Set Result = OBJdbConnection.Execute(SQLQuery)
    your recordset parameters are inherited from the connection, if they are available there (cursor location is inherited, for instance.) And, other parameters are defaulted to values which may not be (and in your case, ARE not) appropriate for your needs. The default recordset uses a read-only, forward-only cursor, for instance. It loads very quickly, and you can iterate through it quickly, but, it may not be what you need.

    Instead, declare the objects explicitly, instantiate them explicitly, adjust any parameters you need to, open them, use them, close them, and explicitly release them when you're done with them.

    Code:
    Dim WO as Long  ' Or, whatever data type best matches the field type for workID...
    Dim rsResult as ADODB.Recordset
    Dim OBJConnection as ADODB.Connection
    Dim sqlQuery as String
    
    sqlQuery = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=F:\Users\redacted\IT_Database.mdb"
    
    Set OBJdbConnection = New ADODB.Connection  ' Explicitly Instantiate the Connection
    OBJdbConnection.Open sqlQuery
    
    ' explicitly instantiate the recordset
    Set rsResult = New ADODB.Recordset
    '  set any parameters as needed
    rsResult.Cursorlocation = adUseClient
    rsResult.CursorType = adStatic
    rsResult.LockType = adLockOptimistic
    ' THEN, open the recordset
    SQLQuery = "SELECT * FROM work_requests_tbl"
    rsResult.Open SQLQuery, OBJdbConnection
    
    ' Now, you can move forwards, backwards, or to any record you need.
    
    'Note that if your query will not return data (update, insert, delete queries) 
    ' you don't NEED a recordset- you can use the connection.execute method instead.
    '
    ' The Do-While loop doesn't need to be inside the IF block, as it won't run if rs.EOF is true...
        With rsResult
            Do While Not .EOF
                    .MoveLast
                    WO = .Fields!workID
            Loop
        End With
    ' OR
      If Not rsResult.EOF
        rsResult.MoveLast
        WO = rsResult.Fields!workID
      End If
    
    '...
    ' After you're done with the database objects...
    ' 
    ' Close and Release them.
    rsResult.Close
    Set rsResult = Nothing
    
    OBJdbConnection.Close
    set OBJdbConnection = Nothing
    Note - the above code is 'air' code, as it was typed directly into the text window here in the forum. (there may be a typo, for instance...)
    Last edited by loquin; 02-02-09 at 15:52.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •