How do I batch .Update?

  • Thread starter Thread starter Sheryl
  • Start date Start date
S

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.
 
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?
Click to expand...

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.
Click to expand...

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
 
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.
Click to expand...

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
 
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.
Click to expand...

Agreed; and 95% of the remaining 10% can be done in SQL too, just that
it's perhaps not simple!
 
I think it's your logic! <g>
Click to expand...

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
 
Back
Top