Query vs. Control Source (Which performs better?)

[dgunner71]

I'm looking for any performance information/recommendation between basing a form on a query (or a Combobox) or just putting the SQL code directly in the control source of that form (or Row Source for combo boxes).

Does one perform faster than the other?
Is there a standard that should be followed?

I think I'm leaning toward doing away with queries and just including all SQL as the control source of that form. Aside from not being able to use the query for multiple forms, is there any downside to doing this?

Thanks in advance for any advice.

Best Regards -

Gunner

[zmbd]

dgunner71:

You have confused the issues here.
Control source is not the same as record source.
The two terms are NOT interchangable.

For the control source of any control on a form, you will be limited to either setting it to one of the fields of the form's record source or leaving it blank.

For a form, you may set this to a table, a stored query, or and embedded query. My choice depends on what I need to have happen; usually, I use a stored query. I will often embed the query in the record source of a form if I want to keep prying fingers from altering the query.

As for the combo/list box:
You need to understand the CombBox Object model.
ComboBox Object : ACC2010
Pay particular attention to the Control source, Rowsource, and bound column properties.

Once again; here for the rowsource, I often embed the query in the rowsource because I want to keep prying fingers from easily altering the query as I am often useing it as a bound lookup field; thus, the value returned absolutly has to be the correct entry for the field.

If you split the front and backends, I generally have the saved queries for the rowsource for the forms and controls in the frontend as these usually do not change; however these still need to access the backend tables to work so I don't think there's any performance gain.

[dgunner71]

zmbd -

Thanks for your reply - I understand the row source/record source clarification; thank you for pointing that out.

I believe you have answered my question -it does not sound as though there is an benefit to creating an 'query object' vs. 'embedding the SQL' as the record source.

My intention is, at least partly, exactly as you said - to keep prying fingers out of the queries. I was just concerned that there might be some performance loss (or gain) by doing so.

Thanks again!

Gunner

[zmbd]

Always a pleasure.

Please keep track of this thread for a few days. Rabbit, Neopa, or one of the others may have a different view and bit better understanding as they program this stuff for a living; whereas, I program this as a secondary role in a lab.