Re: Fastest way to do this?

<eselk@xxxxxxxxxxxx> wrote in message
news:8361fcc7-ad03-46df-8b54-2779c9186aff@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Lets say I've got a list of 50,000 records, and the user has half of
them selected, not in any particular order. I need to update one
field on all selected records. There is a primary key on the table.
What would be the *fastest* way to update those records?

With an update query I'm able to update about 4,000 records per
second. Using a DAO recordset, in a loop, I'm only getting about 500
per second. So obviously an update query would be better, but in this
case there isn't a simple WHERE clause I can use.

My MDB file is on a shared network drive, and multiple users do have
it open at once, so I can't do anything that requires exclusive
access.

I've tried BeginTrans and CommitTrans... although I think I'm in
"transaction" mode by default anyway. I notice I have to call
CommitTrans after about 9,000 records, or the call to Edit fails with
an error code I can't find any info about (0x800A0BEC) and
FormatMessage can't give me any error text for it. Could be "The
required parameter was not provided on the command line or in the
configuration file. ", but that makes no since.

How about the code below.
With the following assumptions:
The table has a field named [Data], defined as date/time.
The form is in data*** view.


Private Sub Form_Current()

'Only run code if multiple records have been selected
If Me.SelHeight > 1 Then

'Ask for confirmation to make changes
If MsgBox("You have selected " & Me.SelHeight & " records." & vbCrLf &
_
"Do you want to update the Data on these records to today's
date?", vbYesNo) = vbYes Then
Dim rst As DAO.Recordset
Dim i As Integer
Set rst = Me.RecordsetClone
rst.MoveFirst

'Move to the first selected record
For i = 1 To (Me.SelTop - 1)
rst.MoveNext
Next

'Change the data for the selected records
For i = 1 To Me.SelHeight
rst.Edit
rst!Data = Date
rst.Update
rst.MoveNext
Next

rst.Close
Set rst = Nothing

End If
End If
End Sub

Let us know if this works and is fast enough.
I did NOT experiment with 50,000 records.
Fred Zuckerman


.

Quantcast