ADO Update Efficiency

Hi All,

I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.

Simply, is it better to update a table via an UPDATE query or Recordset manipulation?

I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?

i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?

It's a MS Access XP app with ADO 2.8.

My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.

I appreciate your help!
Thanks,
WarrenHow about this...

store the keys and values in an access table and join the sql server table to it and perform the update...

UPDATE s
SET Col1 = a.Col1
FROM SQLTable s INNER JOIN AccessTable a
ON s.key = a.key|||Hey Brett,

While that would be efficient, however the data is being populated via a form. To update a "search table" then run the query would include extra transactions: 6 queries/seeks to update the search table then another query to update the main table.

I decided on this:

'con = open connection
Con.Execute "UPDATE ...", , adExecuteNoRecords

I felt this would be more efficient than

'rs open with appropriate connection; seek and index support
rs.Index = "PrimaryKey"
rs.seek "pkval1", "pkval2", adSeekFirstEQ
rs!val1 = newval1
rs!val2 = newval2
rs.update

I really can't find any documentation benchmarking the efficiency of ADO updates; it just doesn't seem to be out there.

-Warren