Saved query Vs SELECT property

On my MS Application application, I have several forms containing combo-boxes (as some of you know from your help last week :O).  Currently, most of these objects have the rowsource set via a SELECT statement in the properties.  My question is this.  Is there any real benefit in converting those SELECT statements, to saved queries?
Andy BrownDeveloperAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Andy,

  There used to be in the past, but no longer.   Behind the scenes, any SQL statement in a rowsource is saved by Access as a temp query def.

  Until that was done, you used a SQL statement when you wanted a costing done each time, and a saved querydef if you wanted a saved costing plan to be used.  

  Now you don't have a choice.

Jim.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you save them as queries, you can use them wherever you wish and moreover you need to tweak them only once if any such need arises instead of tweaking the select statements underneath each and every objects.
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
The benefit of using a stored query is that you can update that query in just one place. This is usefull for something like a "Select employee" combobox, because in the query you can specify the sort order, and maybe have some filters. This saves you having to redo these settings every time you re-use the same combobox. Personally I use a mixture of both saved query and just typing in the select property.

Any query I use for comboboxes I prefix with qlk (for Query Lookup), e.g. qlk_select_employee
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
Jim answered the question. So, I'll just add, that I only use SQL source for the most simple cases, for example to change the ordering.
Saved queries are so much faster to debug and can be reused or easily copy-pasted if you need a modified version.
0
 
PatHartmanCommented:
I normally use querydefs.  Old habits die hard but they are still easier to debug and if you are testing, you have the ability to open the querydef while the form is open and you can't do that with an embedded SQL String.  The final issues is that on more than one occasion I have lost the embedded string because of some Access error.  I have not figured out what caused the error and it doesn't happen regularly which is why can't figure out what causes it but it is is sufficiently damaging that I would NEVER save anything complex directly in the form/report.
0
 
Andy BrownDeveloperAuthor Commented:
As always guys - really great and concise information (and on a Sunday no less).

Thanks everyone - have a great week.
1
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
There are also times when I modify the SQL of either the form (search form results), or the combobox (cascading) alot through code. In those cases it doesn't make much sense to me, to use a stored querydef.

Another reason why I sometimes prefer NOT to use a stored querydef object is that I don't want it to clutter up the navigation interface while developing.
1
 
Andy BrownDeveloperAuthor Commented:
Thank you everyone.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.