What method is better and faster (1 Viewer)

[smig]

If I need to update only a field or two for a specific record in table.
What method is better, faster, cleaner.... ?
Open a recordset or use an Update query ?

 

[Gasman]

I would say query?

 

[June7]

Just to commit data to table, I use CurrentDb.Execute in VBA. I normally only open recordset if I need to read and manipulate existing data.

 

[isladogs]

A query or equivalent SQL statement is ALWAYS far faster as all records are effectively updated at once.
When you loop through a recordset, each record is updated row by row so for a large recordset that can take a very long time.

However for updating a couple of fields in a single record, I doubt you will notice the time difference.
Nevertheless why use a recordset when it requires more code than an update query or update SQL?

 

[The_Doc_Man]

The ONLY time I would use a recordset would be if I had to look at the content of the record to make a decision before doing the update. Otherwise, it would be an UPDATE query every time. And even then, if there is a way to write the WHERE clause to exclude the issue of the decision, then I would use the SQL anyway.

 

[theDBguy]

Hi. I would also prefer using a query, but if you run into problems with delimiters, try using a recordset. Cheers!

 

[Cronk]

Less typing with an update query. Speed on an indexed table is irrelevant.

 

[isladogs]

Hi. I would also prefer using a query, but if you run into problems with delimiters, try using a recordset. Cheers!

Better to sort out the delimiter problem in my opinion

Speed on an indexed table is irrelevant.

Whilst we're all singing from the same hymn sheet, actually that isn't quite true.

Running a SELECT query on an indexed field is certainly much faster - in tests up to 250 times faster
However running action queries on an indexed field will take significantly longer (up to 50% in tests).
This is because the index needs to be updated as well as the individual records.

Nevertheless my previous comments still apply - update queries are faster, the code is cleaner and so are better than using recordsets.
Only use a recordset where it can't be done using an update query / SQL

 

[Cronk]

One record. Four fifths of five eigths of not much.

 

[isladogs]

I was generalising regarding update queries
I made the same point as you earlier regarding query or recordset -see post #4
Similarly, if using an update query for one record, it won't matter much whether the field is indexed or not.