none
ADO and Recordset Errors (every other procedure call) RRS feed

  • Question

  • I am trying to anticapate errors so I can workaround potiential problems.  However the following routine only produces an error every other time.
    anybody know how or why?

    First Execute:  Errors
    Second Execute: No Errors
    Third Execute: Errors
    Fourth Execute: No Errors
    .
    .
    .
    and so on.

    This is what I am wanting a solution for: 
     I have two users, both using a identical recordset.
     The first user to save the data back to the database wins.  .UpdateBatch
     Any other user has to resync the recordset or an error is to occur.

     Of course, this only applies to the users that have obtained a recordset since the last update.

    Any pointers?

    _____________________________________________________________________________________________________________________________________________________
       
     Public Sub Test() 
     ' THE CODE
     
      On Error GoTo Err_Handler
     
      Dim RST1 as New ADODB.Recordset
      Dim RST2 as New ADODB.Recordset
     
      ' Both RST1 & RST2 excutes the same command

      Set RST1 = ...  ' LOCKTYPE ---> adLockBatchOptimistic
      Set RST2 = ... ' CursorType ---> adUseClient
        ' CursorLocation ---> adOpenStatic
        ' CommandType ---> adCmdStoredProc
        ' ActiveConnection ---> CurrentProject.Connection

     

      ' RST1 & RST2 Recordcount > 10
     
      RST1.MoveFirst
      RST2.MoveFirst
      RST2.MoveNext
     
      RST1("<Field1>").Value = "<Value1>"
      RST2("<Field1>").Value = "<value2>"
     
      RST2.MoveFirst
     
      RST1.UpdateBatch

      RST2("<Field1>").Value = "<Value3>"
     
      ' The next line is where the error occurs.
      RST2.UpdateBatch      

     Finish: 
      RST1.Close
      RST2.Close
     
      Set RST1 = Nothing
      Set Rst2 = Nothing
      
      Exit Sub

     Err_Handler:
      MsgBox Err.Number & " ---> " & Err.Source & VbCrLf & Err.Description,vbOKOnly,"Error!"
      Debug.Print RST1.Status  ' Returns 8
      Debug.Print RST2.Status  ' Returns 2050  
      Resume Finish

     End Sub

    _____________________________________________________________________________________________________________________________________________________


    _____________________________________________________________________________________________________________________________________________________

         ERR OBJECT RETURNS:

    _____________________________________________________________________________________________________________________________________________________

                   Err.Number ---> -2147217887
     Err.Number-VbObjectError ---> 3617
                   Err.Source ---> Provider
              Err.Description ---> "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
       Err.HelpContext ---> 1240640
          Err.HelpFile ---> "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1033\VbLR6.chm"
      Err.LastDllError ---> 0


    _____________________________________________________________________________________________________________________________________________________

    _____________________________________________________________________________________________________________________________________________________

         ADODB.Recordset.ActiveConnection.Errors & ADODB.Recordset.ActiveCommand.ActiveConnection.Errors

    _____________________________________________________________________________________________________________________________________________________

     ITEM 1: (1).
     
         Description ---> "One or more properties cannot be returned."
         HelpContext ---> 0
         HelpFile ---> ""
         NativeError ---> 44
         Number ---> -2147217887
         Source ---> "MSDataShape"
         SQLState ---> ""

     Item 2: (2).
     
         Description ---> "Provider does not support the property."
         HelpContext ---> 1240640
            HelpFile ---> ""
         NativeError ---> -2147217887
              Number ---> -2147217887
              Source ---> "ADODB.Connection"
            SQLState ---> ""

    _____________________________________________________________________________________________________________________________________________________

    • Moved by Tim Li Tuesday, August 11, 2009 2:56 AM Unable to move thread
    • Moved by Tim Li Tuesday, August 11, 2009 3:24 AM off-topic (From:Visual Basic for Applications (VBA))
    Thursday, August 6, 2009 4:19 AM

Answers

All replies