Queries or SQL in Form Record Source

[Chertsey]

This questions pertains to the Record source for Forms. Is there any
advantage to bulding actual Queries and saving them as such to be used as the
form record source. I generally build the SQL Statement in each of my forms
record source. Is there an advantoge of one method over the other. I find it
rather cumbersome when I see a large database with so many Queries saved.
What is the best way to proceed.

Thanks

 

[Jerry Whittle]

sIn the early versions of Access, there were performance reasons to use saved
queries instead of SQL statement as the early versions didn't optimize SQL
statements used as recordsed but did so on saved queries.

Now the biggest reason to use saved queries instead of SQL statements would
be if you can use the same query more than once. For example the same query
populating both a report and form would be better than two SQL statements
especially when you need to make a similar design change, such as adding a
field, to both.

 

[Jeff Boyce]

JOPO (just one person's opinion) ...

I find it somewhat more 'obscured' to bury a SQL statement inside a form or
a report, so I tend (not 100%, but a strong tendancy) to create queries
through the design view/user interface. That way, when changes are needed
(e.g., change of requirements, fixes during development, etc.), I don't need
to open the form or report, just the query.

But then, I'm definitely more 'visual' and respond better to the design view
than to raw SQL.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Daugherty]

With all due respect to my friend Jeff, my tendency is to go exactly
the other way. My Database | Queries window is often completely empty
in a finished application.

In days of yore I kept queries independent of Forms and Reports and
gave them meaningful names. I often cleverly used a given query for
both one or more Forms and one or more Reports. Don't you know that
sooner or later the purpose or use of one of those targets would
change a little. I would accordingly find and tweak the appropriate
query.. I was totally dependent on my memory to recall if other
objects used that same query or I would have to have the presence of
mind and patience to stop right then and search for object
dependencies. If I could then determine that I needed another,
similar, query then I would create it. Like most of us, I often
blundered ahead without making sufficient careful checks and ended up
breaking all of the other objects that depended on the original query.

Now, where ever possible, I *always* embed the query in the object
that uses it. That way I am confident that when I modify that query I
affect no other objects. My life is less exciting and more
predictable.

Your mileage may vary.

HTH