What method is better and faster (1 Viewer)

smig

Registered User.
Local time
Today, 18:34
Joined
Nov 25, 2009
Messages
2,209
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 ?
 

June7

AWF VIP
Local time
Today, 08:34
Joined
Mar 9, 2014
Messages
5,423
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

MVP / VIP
Local time
Today, 16:34
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 28, 2001
Messages
26,999
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

I’m here to help
Staff member
Local time
Today, 09:34
Joined
Oct 29, 2018
Messages
21,358
Hi. I would also prefer using a query, but if you run into problems with delimiters, try using a recordset. Cheers!
 

Cronk

Registered User.
Local time
Tomorrow, 03:34
Joined
Jul 4, 2013
Messages
2,770
Less typing with an update query. Speed on an indexed table is irrelevant.
 

isladogs

MVP / VIP
Local time
Today, 16:34
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Tomorrow, 03:34
Joined
Jul 4, 2013
Messages
2,770
One record. Four fifths of five eigths of not much.
 

isladogs

MVP / VIP
Local time
Today, 16:34
Joined
Jan 14, 2017
Messages
18,186
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.
 

Users who are viewing this thread

Top Bottom