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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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.
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.
Andy BrownDeveloperAuthor Commented:
As always guys - really great and concise information (and on a Sunday no less).

Thanks everyone - have a great week.
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.
Andy BrownDeveloperAuthor Commented:
Thank you everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.