Query Best Practices

[warmexxus]

I've been developing an application for almost 2 years now. It's come along very nicely. I recently needed to filter a report based on external data in another table, and the solution was to use a query. It solved my problem and also enlightened me to how powerful queries can be. My general question is are queries an integral part of any Access application? Should I have been looking for more uses for queries? What I've done up to this point is code everything in VBA as I tend not to venture into automatic processing objects like queries and macros. I really haven't looked into those topics. So here I am and I would like to get a grasp on this topic because maybe I've been missing out on so much of what Access has to offer.

So what is the generally accepted use of queries? Are they faster than filtering forms bound to a recordset?

Any help here would be greatly appreciated...

Thank you,
Dan Murphy

 

[boblarson]

Queries are a relational database's workhorse. It is as important to databases as breathing is to a person. You really can't live without them. If you try you are starving yourself of essential nutrients.

 

[boblarson]

By the way, a form's recordset is a query (actually even if you only use a table, which if you view a table in Access is only a query of the data - you really aren't seeing the table itself).

 

[gemma-the-husky]

99% of the time in access, the answer ... is a query!

 

[HiTechCoach]

Are you creating your the SQL "on the fly"?

One of the advantages a saved query (like a query object, in form or report's record source, row source of a combo box, etc) is that it gets optimized by JET before it is saved.

SQL generated "on the fly", must be run through the JET optimizer every time it is executed.

 

[warmexxus]

On the fly has been my common practice. Sadly I also used .EOF loops through recordsets for updating when a simple query would have worked 10x faster. But as the days go by I am learning.

 

[Banana]

It's quite common for people to think that it's easier to write SQL on the fly in VBA, missing out on the real potential of parameter queries which means coding and better performance. It is possible to reference controls on a form in a query or just use a plain parameter and execute the query with the parameters.