Discussion: DoCmd.RunSQL vs RecordSet

[drkhelmt]

For those that know Access better than I...

I was wondering, form a coding or database level standpoint, is it better do do updates, inserts, etc. in VBA, by using the DoCmd.RunSQL (sqlstatement) or is it better to use Recordsets?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[dhookom]

Recordsets are not nearly as efficient as SQL queries. Recordsets provide a little more functionality in some cases however if an action query is possible, I would use it over a recordset any day.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

Duane is correct. SQL is the "native" language of relational databases. But you can sure add more procedure with recordsets. Also, DoCmd.RunSQL works best with action queries and does not work well with simple SELECT.

Want some fun - try executing a simple SELECT * FROM ... statement with the RunSQL method. I am sure there are other solutions, but I had to use a querdef to achieve this.

Richard

 

[drkhelmt]

Well shoot, I thought this might be a not so cut and dry issue. Thanks for responding though.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[BNPMike]

Access (ie Jet) further distorts the argument as it is relatively good at letting you update tables participating in joins, and it has a very fast SQL interface (ie QBE).

Very often you think "I must programme this process" only to realise you can actually knock together 4 queries and run them to solve the problem in 5 mins. Not elegant or satisfying but it means with the time you've saved, you can go down the pub....