rs.Update or SQL Update?

[PJFry]

As I learn more about coding, I see a lot of examples where folks use a
recordset to update a table rather than write a SQL statement and use
docmd.RunSQL. Just from a syntax standpoint, the rs.update method seems much
easier for me. Is there another, more technical reason to use over the other?

Thanks!
PJ

 

[Klatuu]

Executing SQL statements is faster than using a recordset, even for one
record. If you have a bulk insert, update, or delete, it is significantly
faster. The only real reason to use recordset processing is when complex
logic is required for each record. Also, in stead of the Docmd.RunSQL, you
will find that:

Currentdb.Excecute sqlstatment, dbFailOnError

Is much faster. It bypasses the Access User Interface and goes directly to
Jet (or SQL Server, etc). Consequently, the SetWarnings setting is
unaffected. That is, there will be no message asking if you really want to
do what you want to do.

In addition, the Execute can use either a hand written SQL statement or a
stored query. Stored queries are faster than hand written SQL. A stored
query has already been compiled and optimized. A hand written SQL statment
has to be compiled and optimized before it is actually executed.

 

[Tony Toews [MVP]]

In addition, the Execute can use either a hand written SQL statement or a
stored query. Stored queries are faster than hand written SQL. A stored
query has already been compiled and optimized. A hand written SQL statment
has to be compiled and optimized before it is actually executed.

But is that a significant difference? IOW have you done timing
tests? These last number of years I've been coding all my action
queries as VBA strings.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Klatuu]

I am quoting from Access 2002 Developers Handbook, Litwin, Getz, and Gunderloy.
No, have not done any independant testing. I figure they know what they are
talking about.

Reasonable question, though, Tony. Perhaps it deserves some verification.

I also recall the same advice back in the FoxPro days. Access uses the same
Rushmore technology for query optimization, so it makes sense to me.

 

[Bob Quintal]

But is that a significant difference? IOW have you done timing
tests? These last number of years I've been coding all my action
queries as VBA strings.

Tony

I've seen marked improvements in speed by using a stored query to
replace an SQL string executed from VB.

Then again, a loop to add new records via a recordset.addnew may be
much faster than calling a query within the loop.

Overall, I think that the results obtained will vary depending on
the circumstances of what processing is beng applied to what data.

 

[Tony Toews [MVP]]

I am quoting from Access 2002 Developers Handbook, Litwin, Getz, and Gunderloy.
No, have not done any independant testing. I figure they know what they are
talking about.

Reasonable question, though, Tony. Perhaps it deserves some verification.

I also recall the same advice back in the FoxPro days. Access uses the same
Rushmore technology for query optimization, so it makes sense to me.

Sure, it makes sense. But if it's, say, 50 ms on a 1 Ghz machine
which is run twice a day on average what do I care?

I've ended up using action queries in code because it's much easier to
develop with and debug compared to action queries with parameters.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I've seen marked improvements in speed by using a stored query to
replace an SQL string executed from VB.

That that's subjective. I'd like to see some peer reviewed tests
with the MDB available to all to test on their own systems.

Then again, a loop to add new records via a recordset.addnew may be
much faster than calling a query within the loop.

Of course.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/