A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SQL and Queries



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2009, 05:00 AM posted to microsoft.public.access.tablesdbdesign
Brian Carlson
external usenet poster
 
Posts: 49
Default SQL and Queries

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
  #2  
Old February 11th, 2009, 05:49 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default SQL and Queries

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian Carlson" wrote in message
...
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


  #3  
Old February 12th, 2009, 02:55 AM posted to microsoft.public.access.tablesdbdesign
Brian Carlson
external usenet poster
 
Posts: 49
Default SQL and Queries

Thank you for the clarification Allen. I think I have a better handle on
when I would want to each of these.

Brian

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian Carlson" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.