Queries or SQL?

[Malcy]

I wondered which was generally considered to be more efficient and/or better practice.

To run queries from DoCmd.OpenQuery
or
To run SQL code from cnn.Execute

If using SQL there are fewer objects (queries) in the database but I don't know if this is an advantage or otherwise. Equally it strikes me neither is particularly harder than the other to set up so which is best to use?

Am at a point in a new project where I could use either (I think) and would value any views on pros and cons.

Thanks

Malcy

 

[fpendino]

I usually base my designs on how long the text of the sql is and whether or not need to use any variables. I'm not sure which of the two is quicker, but with my experience, they usually run about the same. I would guess that the sql in code may run a slight quicker, but maybe one of the pros could varify that.

Keep in mind that if you have to update queries often, sometimes it's quicker to make changes while in the design view, but then again it just depends. I have several that could go either way and if they are simple sql statements, I will usually through into the code and execute them. Good Luck.