How do I batch .Update?

[Sheryl]

My VBA program uses recordsets defined via e.g.
'Dim rs as DAO.Recordset', and I am updating several
fields in about 3000 records. For each field I must do a
lookup in another even larger file using the primary key
of the record.

I'd like to batch this process, by changing (.Edit) one
field at a time and saving (.Update) them all at the end.
I see from the Help that .Update supports a batch option,
but how do I use it?

When I use an update SQL statement instead of VBA it goes
like lightning, and I'm sure it's because all the records
are updated at the end.

 

[Dev Ashish]

I'd like to batch this process, by changing (.Edit) one
field at a time and saving (.Update) them all at the end.
I see from the Help that .Update supports a batch option,
but how do I use it?

I don't think that's the option you would want to use (dbUpdateBatch). It
has nothing to do with transactions, instead it allows you to 'flush'
changes to disk programmatically (typically, Jet takes care of it for you
in the background).

DAO supports transactions for Table type recordsets. Check help for
BeginTrans and CommitTrans; the example shows you how to use transactions.

When I use an update SQL statement instead of VBA it goes
like lightning, and I'm sure it's because all the records
are updated at the end.

You can use transactions for SQL statements as well; or if it's a single
sql statement, then check the dbFailOnError option for the Execute method
in help. SQL is typically preferred for such bulk operations btw.

-- Dev

 

[Tim Ferguson]

I am updating several
fields in about 3000 records. For each field I must do a
lookup in another even larger file using the primary key
of the record.

Ninety percent plus of jobs like this can be done in a single SQL command,
which will certainly be quicker as well as more robust.

Tim F

 

[John Vinson]

Ninety percent plus of jobs like this can be done in a single SQL command,
which will certainly be quicker as well as more robust.

Agreed; and 95% of the remaining 10% can be done in SQL too, just that
it's perhaps not simple!

 

[Michael Hopwood]

That leaves 0.5% right? <g>

 

[John Vinson]

That leaves 0.5% right? <g>

Yep. That's why I've learned recordset updating code!

 

[Tim Ferguson]

That leaves 0.5% right? <g>

No: 0.05 * 0.05 = 0.0025 equiv 0.25%

or is this my Pentium Floating Point bug again...?


Tim F

 

[Michael Hopwood]

I think it's your logic! <g>

 

[Tim Ferguson]

I think it's your logic! <g>

Actually, it's me not remembering my own posts.... but I couldn't work out
how to recall a sent message in XNews.

<dunce>


Tim F