[AccessD] RecordsetClone and Bookmark

Asger,

    Another point of interest:  Form' recordset can be sluggish regarding it's bookmark. 

    For consistent behavior while synchronizing RecordsetClone with current record on the form, it should be done against form's bookmark (not form's recordset's bookmark).

' Preferred syntax:
        Me.RecordsetClone.Bookmark = Me.Bookmark

' Risky syntax:
        ' Me.RecordsetClone.Bookmark = Me.Recordset.Bookmark

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Asger Blond 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, February 08, 2012 15:49
  Subject: Re: [AccessD] RecordsetClone and Bookmark


  Hi Gustav

  Then we agree, I guess - I wouldn't consider calling an SQL update when looping the clone, then I would just make the Edit/Update on the clone. My point was actually a batch update, where an SQL Update is by far the most efficient.

  / Asger

  -----Oprindelig meddelelse-----
  Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
  Sendt: 8. februar 2012 10:56
  Til: accessd at databaseadvisors.com
  Emne: Re: [AccessD] RecordsetClone and Bookmark

  Hi Asger

  Oh, two different things:

  > .. I would consider looping the recordset to change some values ..
  .. calling an SQL update for each record that needs an update [my next thought]

  That is quite different from a batch update.

  /gustav


  >>> ab-mi at post3.tele.dk 08-02-2012 10:32:53 >>>
  Hi Gustav
  Can't confirm this. I just made a test updating 3500 records on a table with total 10000 records.
  Execution times:
  Update with loop on a RecordsetClone (DAO Edit/Update): 3.42 sec.
  Update with SQL (ADODB Execute): 0.03 sec.

  Here are the test-procs:

  Private Sub cmdUpdate_Loop_Click()

      Dim rst as DAO.Recordset, dtmTime As Single
      dtmTime = Timer

      Set rst = Me.RecordsetClone
      rst.FindFirst ("SalesPerson = 'Jones'")
      Do Until rst.NoMatch
          rst.Edit
          rst.Fields("SalesPerson").Value = "X"
          rst.Update
          rst.FindNext ("SalesPerson = 'Jones'")
      Loop
      Debug.Print "Loop update: " & vbTab & Timer - dtmTime

  End Sub


  Private Sub cmdUpdate_SQL_Click()

      Dim cnn As ADODB.Connection, dtmTime As Single
      dtmTime = Timer

      Set cnn = CurrentProject.Connection
      cnn.Execute "UPDATE tblSale SET SalesPerson = 'X' WHERE SalesPerson = 'Jones'"

      Debug.Print "SQL update: " & vbTab & Timer - dtmTime

  End Sub


  Maybe this is not the way you would do an update on a RecordsetClone - ?

  / Asger


  -----Oprindelig meddelelse-----
  Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
  Sendt: 8. februar 2012 08:32
  Til: accessd at databaseadvisors.com 
  Emne: Re: [AccessD] RecordsetClone and Bookmark

  Hi Asger

  You should use RecordsetClone for this - way faster than calling SQL updates. And the form will be updated as well automagically.

  /gustav

  >>> ab-mi at post3.tele.dk 07-02-2012 23:06 >>>
  If for instance I would consider looping the recordset to change some values, then using RecordsetClone and Bookmark would be more efficient than using Me.Recordset because the form wouldn't have to be updated for each move. (But actually I wouldn't use a recordset Edit/Update but a SQL Update for this...).