DoCmd.RunSQL or db.Execute or OpenRecordset.....Which should I use???

[Toga]

Can anyone share with me (in on or 2 paragraphs) a little insight on the decision process I need to go through in order to decide which Query process I should use for a given task. Is it all a matter of preference or is there some basic reasons for choosing one over the other.

I've been through the help screens and they give plenty of detail on each but I can't seem to find anything that compares one against the other.... I'll buy a book if need be but I prefer to hear the answer based upon peoples opinions and experiences. In other words....I want the down n dirty that the books sometimes miss!

PS....I plan on learning VB6 in the near future if that's a consideration as well.

Thanks for everyone's help in advance.....you've been GREAT thus far!!!!!

 

[RickSpr]

If you have the choice, OpenRecordset is inferior to the other two, because it involves VBA interpreting compiled code within your record loop. (Even though VBA is now compiled, there is still plenty of interpreter-like activity going on. For instance, the compiler doesn't know what fields will be in the recordset at execution time, so it has to compile the field names into the executable code and look them up in the recordset object at run time.)

I wouldn't think there's very much different between RunSQL and Execute, though. RunSQL is an Access function, while Execute is a DAO function, but I would think that RunSQL simply calls Execute against the current database. So why do both exist? Because RunSQL (like most DoCmd methods) is available to Access macros, while Execute is only available to code. Rick Sprague

 

[link9]

Excellent explanation, but (correct me if I'm wrong, by all means), an ADO approach is probably desireable with it's future in dotNet and usefulness in other approaches such as web development. Easy to learn, very useful, and the dominant platform right now, and what looks like will be the dominant platform for a while to come.

Is DAO not pretty much depracated??

--
Paul Prewett

 

[RickSpr]

Paul, I wouldn't presume to correct you, as my knowledge of ADO is nil so far. I'm not in a position to judge whether the deprecation of DAO is based on real inferiority, or on Microsoft's marketing division's need to promote its latest technology.

Actually, isn't "deprecation" rather a strong word for it? I haven't perceived any ADO snobbishness at all, prior to this. In fact, on the forums I monitor here on Tek-Tips, I'd have to say that, where the data access architecture is evident in a post, something like 70% are using DAO. For that matter, a majority of users still seem to be using Access 97, so ADO isn't even available to them. ADO may be the flavor of the day, but DAO won't be going away any time in the immediate future.

In any case, Toga's question was about the relative merits of using a Recordset, RunSQL, and Execute. Whether the recordset is DAO or ADO doesn't make any significant difference, with regard to the inefficiency of interpretive execution. Rick Sprague

 

[link9]

Fair enough.

I am fairly new to the "programming community" and am still trying to feel out what's good to spend my time studying and practicing.

And yes, deprecation probably was a bit too strong.

Thanks for your input, Rick.
Paul Prewett

 

[Toga]

Hey Thanks for your input guys.... I will assume user preference wrt RunSQL or db.Execute but doesn't OpenRecordset still have some advantages over those options which might make it an option of choice?

For example, what I'm thinking about are with Transactions.
With OpenRecordset, you can rollback Updates to a database where you cannot with either of the other 2. (I think)

Are there maybe other cases or examples where you would lean towards using OpenRecordset over the other 2? I guess I'm just trying to get as much insight as possible to aid in my decision making process on which I should be using.....Rules of thumb if you will.

 

[RickSpr]

Actually, you can wrap db.Execute in BeginTrans and CommitTrans yourself; I think that would give you the all-or-nothing update. RunSQL has a 'use transaction' parameter.

But yes, certainly, OpenRecordset is still useful. I use it much more than I use the others, in fact.

The reason is, RunSQL and Execute can only be used for "action queries", that is, queries which insert, update, or delete records. They're primarily for bulk updating of the database. And they don't return any data to your code. Most of the time, if your code behind a form needs database access, it needs to read data, which you can't do with RunSQL or Execute. Even if you're updating, you're usually only updating one record at a time in code. With only one record, there's no practical difference between using a recordset or an action query; the user won't see any difference in response time.

If you're updating a lot of records, do it with RunSQL or Execute if you possibly can. Recordset processing is noticeably slower in this situation. Besides, writing a recordset loop is harder!

But if you're only updating a handful of rows, use a recordset if it's more convenient. And if you need to read the data, the recordset is your only option.

It's late and I'm tired. I hope I haven't been rambling here.
Rick Sprague

 

[Toga]

Hey Thanks Rick.....That's precisely the insight I was searching for!

I've been using DoCmd.RunSQL alot (many times for one record) and in several instances......probably forcing it to do what I want in situations where I should have been using OpenRecordset.

I somehow had the perception that DoCmd.RunSQL or db.Execute were better ways to skin a cat if you will.....until I got into some of those situations.

Anyway, I started questioning my approach to things here and the Access help screens really aren't too bad as long as you already have a pretty good idea or insight into what your doing......but if not or if your a newbie like me, they're very difficult to decipher any insight from.

I'm probably the one starting to ramble here so I'll close with Thanks Again!

Toga

 

[Quehay]

I believe that I just read the other day, having Toga's same question in mind, that the difference is that RunSQL only works on CurrentDB(), whereas execute can be performed against an external db, once the proper workspace, etc. has been set.

RE: DAO vs ADO For working with Access within Access DAO is very friendly and it it's not as buggy as ADO. ADO is cool and I've done some with it, but until the Jet 4.0 provider the connections were problematic. DAO works great and there are lots of routines out there to borrow from/adapt. I'm a neophyte programmer so I'm not the village sage when it comes to programming, but a lot of this Microsoft "new wave ride it or get lost with the COBOL dweebs" sort of stuff seems less like Shinola than the other alternative. Remember, just like stockbrokers/financial planning outfits who couch their pitch in terms that seem to address your well-being, what is being sought is their well-being.