SQL and Queries

  • Thread starter Thread starter Brian Carlson
  • Start date Start date
B

Brian Carlson

Please clarify: A query is simply an SQL statement. However, it is an SQL
statement that is 'public'...I mean public in the sense that you could have
multiple objects referring to this query. However, It is also possible to
simply reference an SQP statement rather than an actual query that is one can
simply type an SQL statement in the Row source rather than referencing a
query?? Could someone please clarify? I am wondering when it is better to
use each of these, or if this is simply a matter of preference? Thank you in
advance for the clarification.

Brian
 
Yes: you can type a SQL statement directly into the RecordSource or
RowSource property (for a form/report or combo/listbox respectively.)

In reality, Access saves those as hidden queries for those anyway. The don't
show up on the Queries tab of the Database Window/Navigation Pane, but you
can see them in a query such as this:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type = 5
ORDER BY MSysObjects.Name;

As to which is better, it's purely a matter of style (since they are saved
anyway.) Some people prefer having fewer saved queries to search through in
the DB window.

My personal practice is to always use saved queries for the RowSource,
because it gives me *one* canonical source to manage for all similar
combos/listboxes throughout the app. For example, if we suddenly decide to
sort inactive clients to the bottom of the list, I have just one query to
change, and all the client combos throughout the application behave the
same. I'm less pedantic about the RecordSource, e.g. I do tend to type the
SQL statement there in forms where the RecordSource will be changed
programmatically.
 
Thank you for the clarification Allen. I think I have a better handle on
when I would want to each of these.

Brian
 
Back
Top