Re: Fastest way to do this?

eselk@xxxxxxxxxxxx wrote:

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.

There is a difference between a query and a recordset. So if you have them "selected" I suppose that's a recordset. Could you not use an update query instead?
Dim dbs As Database
Dim strSQL As String

set dbs = Currentdb
strSQL = "UPDATE Table1 SET Table1.SDate = Date() Where...;"

dbs.Execute strSQL,dbFailOnError
.