NOT YOUR STANDARD QUESTION: SQL UPDATE in Disconnected Recordset

From: Alain Beaulieu (abeaulieu_at_montreal.ds.adp.com)
Date: 03/30/04

  • Next message: William \(Bill\) Vaughn: "Re: NOT YOUR STANDARD QUESTION: SQL UPDATE in Disconnected Recordset" 
    Date: 29 Mar 2004 17:22:55 -0800

    Hi all,

    I VERY often create a disconnected recordset in code from scratch,
    with no database connection, and with no intention to connect either
    (for sorting, for example). What I want to do here for the first time
    is issue an UPDATE "statement" on the recordset itself. Here is an
    example:

        Dim oRecordset As Recordset
        Set oRecordset = New Recordset
        
        oRecordset.Fields.Append "Name", 200, 255
        oRecordset.Fields.Append "Age", 200, 255
        oRecordset.Open
        oRecordset.AddNew
        oRecordset("Name") = "Bob"
        oRecordset("Age") = "34"
        oRecordset.Update
        
        oRecordset.AddNew
        oRecordset("Name") = "Brian"
        oRecordset("Age") = "35"
        oRecordset.Update
        
        oRecordset.AddNew
        oRecordset("Name") = "Bill"
        oRecordset("Age") = "41"
        oRecordset.Update
        
        oRecordset.Sort = "Age Desc"
        oRecordset.MoveFirst
        
        Do While Not oRecordset.EOF
            'some code here...
            oRecordset.MoveNext
        Loop

    I would like to know if I can do an UPDATE on the recordset itself,
    somehow? For example:

    UPDATE *WHATEVER* SET AGE = 40 WHERE AGE > 40

    This would translate into something like:

    oRecorsdset.*SomeUpdatingMethod* = "Age = 40 WHERE Age > 40"

    Can I do this? If so, how? (I don't think so, but I want confirmation
    --> I've done a lot of research and I'm used to the Recordset object)

    I know I can do a loop and update the records individually, but the
    point here is that I want to use an UPDATE statement that the
    recordset would somehow interpret.
    Thanks.

  • Next message: William \(Bill\) Vaughn: "Re: NOT YOUR STANDARD QUESTION: SQL UPDATE in Disconnected Recordset"

    Relevant Pages

    • Re: DAO MUCH faster than ADO in this test
      ... DAO is well-known to be faster than ADO. ... Of course the DAO loop ran faster than the SQL loop; ... advantage of a table-type recordset, which only works on local tables. ... Dim starttime As Single, finishtime As Single ...
      (microsoft.public.access.modulesdaovba)
    • Re: Do While Loop nested in another While causes error
      ... still doesn't explain why a nested DO loop would cause problems. ... Until you sort out the contents of the recordset, ... idHolder = rsGuestbook ... display that new ID. ...
      (microsoft.public.inetserver.asp.db)
    • Re: One more ! Calculating Recordset Values
      ... Well I am almost there lol, got the recordset to work properly returning all ... the values in the one "loop" so to speak and I can see them being returned ... >> Dim aTot, eTot, iTot, pSubTot, aStrengthSubTot, PTotal, SubTotal, ... >>> totals ...
      (microsoft.public.frontpage.programming)
    • Re: One more ! Calculating Recordset Values
      ... EOF means that the end of the RecordSet has been reached. ... > 'Total = The sum of all the SubTotals ...
      (microsoft.public.frontpage.programming)
    • Re: Do While Loop nested in another While causes error
      ... still doesn't explain why a nested DO loop would cause problems. ... Until you sort out the contents of the recordset, ... display that new ID. ... response.Write the value of idHolder within the loopto see if it ...
      (microsoft.public.inetserver.asp.db)