[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...).


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com