By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,116 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,116 IT Pros & Developers. It's quick & easy.

As a generale rule - Query or VBA?

P: n/a
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
Aug 11 '06 #1
Share this Question
Share on Google+
30 Replies


P: n/a
if they're action queries I won't use elsewhere, I just use

DBEngine(0)(0).Execute "SQL Statement...", dbFailOnError

Aug 11 '06 #2

P: n/a
I think a good portion of Access people do what you do, however it is
better to write and execute the SQL in code when possible. It reduces
clutter, but it makes it more difficult for lesser-experienced people
to debug issues.

Paul H wrote:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
Aug 11 '06 #3

P: n/a
Prepared queries are more efficient than executing SQL prepared on the fly.

The problem is that as you say the d window gets cluttered up, I suspect
most people with experience use a mixture of methods.
--

Terry Kreft
"Paul H" <no****@nospam.comwrote in message
news:G6********************@eclipse.net.uk...
I seem to end up with loads of append and update queries just because
it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul


Aug 11 '06 #4

P: n/a
-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entries_Before_a_date.

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects. If
the query is saved, then JET will save the fastest execution path. If
its in code, the JET engine first has to ensure it is valid SQL, then
determine the execution path, then execute it. Which sounds faster?

Granted, for smaller tables, it might not make a noticeable difference.
But, for scalability concerns, it is much easier to use queries and
then move them to SQL Server instead of code. Using code will not
achieve any performance gains with SQL then with Access.

I'm trying to declutter my code using procedures. The database window
is not a concern for me, especially with the Object Dependencies tool
now. I'd hate to think I tried cleaning up the database window and
deleted a query that some code required.

Chris Nebinger


Paul H wrote:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
Aug 11 '06 #5

P: n/a
ch************@gmail.com wrote:
-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entries_Before_a_date.

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects. If
the query is saved, then JET will save the fastest execution path. If
its in code, the JET engine first has to ensure it is valid SQL, then
determine the execution path, then execute it. Which sounds faster?
[snip]

I completely disagree. The speed difference is neglible and the
self-documenting nature of SQL executed in code is a HUGE advantage over a
stored query def. When looking at that query months later you will have to
search to find out where it is called (if at all), then research some more
to determine if a change will break some other part of the app that you
didn't remember also used the same query. SQL executed in code is self
explanatory and has an obvious scope.

I use saved querydefs only when I have to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Aug 11 '06 #6

P: n/a
rkc
ch************@gmail.com wrote:
-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entries_Before_a_date.

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects.
You can also hide a query by right clicking it, selecting properties and
checking attributes: Hidden. Hidden Objects has to be not selected in
Tools, Options, View, Show.
Aug 11 '06 #7

P: n/a
"Paul H" <no****@nospam.comwrote in
news:G6********************@eclipse.net.uk:
I seem to end up with loads of append and update queries just
because it's quick and easy to build queries or make a new ones
based on an existing query.
I store few action queries, but lots of SELECT queries.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #8

P: n/a
"Terry Kreft" <te*********@mps.co.ukwrote in
news:Dc********************@karoo.co.uk:
Prepared queries are more efficient than executing SQL prepared on
the fly.
Is that really a significant issue in most cases? I have never seen
any real performance drain from using DAO for things.

Does it really take that long to optimize SQL?

Keep in mind also that if you're sending the SQL to a back-end
server database, those often will optimize and cache the temporary
query, as long as the SQL remains the same (dunno if they can do it
with queries that differ only in the values in the WHERE clause).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #9

P: n/a
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:%l*******************@twister.nyroc.rr.com:
You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.
Doesn't every developer work with all system and hidden objects
showing?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #10

P: n/a
rkc
David W. Fenton wrote:
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:%l*******************@twister.nyroc.rr.com:

>>You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.


Doesn't every developer work with all system and hidden objects
showing?
I have never looked over every developer's shoulder so I can't say
for sure.

I sure don't.
Aug 11 '06 #11

P: n/a
Hello People,

I agree with you Rick, I was recently working on a database that had
already been developed. It had a user-unfriendly GUI and over 400
queries, most of which he (the previous developer) had no idea what
they did and if they could have been gotten rid of. He used to create
a new query every time he wanted to look something up rather than look
up the old one, which when you are looking at a selection of over 400 I
can understand, but if he had used a proper naming convention he would
not be in that situation. But we are not talking about just having a
descent naming convention here we are talking about freeing the Db from
clutter and personaly I would go with code ever time.

Good luck

Nick

Rick Brandt wrote:
ch************@gmail.com wrote:
-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entries_Before_a_date.

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects. If
the query is saved, then JET will save the fastest execution path. If
its in code, the JET engine first has to ensure it is valid SQL, then
determine the execution path, then execute it. Which sounds faster?
[snip]

I completely disagree. The speed difference is neglible and the
self-documenting nature of SQL executed in code is a HUGE advantage over a
stored query def. When looking at that query months later you will have to
search to find out where it is called (if at all), then research some more
to determine if a change will break some other part of the app that you
didn't remember also used the same query. SQL executed in code is self
explanatory and has an obvious scope.

I use saved querydefs only when I have to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 12 '06 #12

P: n/a
Hello People,

I tend not to store any action queries. Just parameterised SELECT
queries. This is for my reporting needs. If I have a front end
database that does not have any reports and is just used for collecting
data (the reports will be in a different front end) then I will not
have any queries saved.

Good Luck

Nick

David W. Fenton wrote:
"Paul H" <no****@nospam.comwrote in
news:G6********************@eclipse.net.uk:
I seem to end up with loads of append and update queries just
because it's quick and easy to build queries or make a new ones
based on an existing query.

I store few action queries, but lots of SELECT queries.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 12 '06 #13

P: n/a
Hello David

I certainly do work with all my objects showing David, how else do you
know what's in your database?

Nick
David W. Fenton wrote:
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:%l*******************@twister.nyroc.rr.com:
You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.

Doesn't every developer work with all system and hidden objects
showing?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 12 '06 #14

P: n/a
And another thing is that if at some stage you are going to upgrade
your Access database to SQL Server then will have to really a lot more
heavily on update queries. I say update queries, but in SQL Server
they are generally called 'Stored Procedues' or 'Sprocs'.

Nick

Paul H wrote:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
Aug 12 '06 #15

P: n/a

"Rick Brandt" <ri*********@hotmail.comschreef in bericht news:KS*****************@newssvr25.news.prodigy.ne t...
ch************@gmail.com wrote:

I completely disagree. The speed difference is neglible and the
self-documenting nature of SQL executed in code is a HUGE advantage over a
stored query def.
The 'self-documenting nature of SQL' is *not always* clearer than looking at the query-grid for me.

I once had a co-worker who IMO was a bit 'over the edge' with this.
This co-worker copied the SQL of almost every stored query in the sub or function where it was used.
We had LOTS of lines of code but indeed fewer query's in the dbwindow...
On complex query's where lots of links and tables are used this often looks like 'spaghetti' to me.
I sometimes find myself having to debug the SQL and paste the SQL string in a new query only to find out what the code is doing ...
Example at the end of this post

So I use both ways: Stored query's AND code.
For the querydefs I use names that are 'understandable' and consequently (qryInvoice_1 qryInvoice_2 qryInvoiceEnd and such)
In the dbwindow these query's show up consequently
I also use Rick Fisher's Find & Replace to get rid of any object (not only query's) not really in use in the aplication.
Just my 2P.

Arno R
Example of a 'not-self-documenting-to-me' SQL code fragment:

strSQL1 = "SELECT 1 AS Expr1, '" & Format(Forms!FrmFactuurnummer!invDatum, "ddmmyyyy") & "' AS dat, "
strSQL1 = strSQL1 & " TempTabFactBerekening_N.Jaar, TempTabFactBerekening_N.Tijdvak, "
strSQL1 = strSQL1 & " TempTabFactBerekening_N.DebNr, H_OpbrengstRekening_Suppl.OpbrengstRekNr, "
strSQL1 = strSQL1 & " TabSupplementtypen.Prijs, "
strSQL1 = strSQL1 & " TabSupplementtypen.SupplementTypeID,"
strSQL1 = strSQL1 & " CLng(CVDate(TabSupplementenPerGebruiker.Ingangsdat um)) AS Ingdat, "
strSQL1 = strSQL1 & " CLng(CVDate(Nz(TabSupplementenPerGebruiker.Einddat um))) As Einddat,"
strSQL1 = strSQL1 & " TabSupplementen.SupplementID, KliŽnten.Betaalwijze"
strSQL1 = strSQL1 & " FROM TabSupplementen INNER JOIN ((((TempTabFactBerekening_N "
strSQL1 = strSQL1 & " INNER JOIN KliŽnten ON TempTabFactBerekening_N.DebNr = KliŽnten.DebNr) "
strSQL1 = strSQL1 & " INNER JOIN TabSupplementtypen ON TempTabFactBerekening_N.SupplementtypeID = TabSupplementtypen.SupplementTypeID) "
strSQL1 = strSQL1 & " INNER JOIN H_OpbrengstRekening_Suppl ON (TabSupplementtypen.SupplementTypeID = H_OpbrengstRekening_Suppl.SupplementtypeID) "
strSQL1 = strSQL1 & " AND (KliŽnten.DebiteurGroep = H_OpbrengstRekening_Suppl.DebiteurGroep)) "
strSQL1 = strSQL1 & " INNER JOIN TabSupplementenPerGebruiker ON KliŽnten.Klientnummer = TabSupplementenPerGebruiker.Klientnummer) "
strSQL1 = strSQL1 & " ON (H_OpbrengstRekening_Suppl.SupplementtypeID = TabSupplementen.SupplementtypeID) "
strSQL1 = strSQL1 & " AND (TabSupplementtypen.SupplementTypeID = TabSupplementen.SupplementtypeID) "
strSQL1 = strSQL1 & " AND (TabSupplementen.SupplementID = TabSupplementenPerGebruiker.SupplementID)"
strSQL1 = strSQL1 & " WHERE TabSupplementtypen.Frequentie=4"
strSQL1 = strSQL1 & " AND (IsNull(TabSupplementenPerGebruiker.Einddatum)"
strSQL1 = strSQL1 & " OR TabSupplementenPerGebruiker.Einddatum >" & lngBegindatum & ")"
strSQL1 = strSQL1 & " GROUP BY 1, '" & Format(Forms!FrmFactuurnummer!invDatum, "ddmmyyyy") & "' , "
strSQL1 = strSQL1 & " TempTabFactBerekening_N.Jaar, TempTabFactBerekening_N.Tijdvak, "
strSQL1 = strSQL1 & " TempTabFactBerekening_N.DebNr, H_OpbrengstRekening_Suppl.OpbrengstRekNr, "
strSQL1 = strSQL1 & " TabSupplementtypen.Prijs, "
strSQL1 = strSQL1 & " TabSupplementtypen.SupplementTypeID,"
strSQL1 = strSQL1 & " CLng(CVDate(TabSupplementenPerGebruiker.Ingangsdat um)), "
strSQL1 = strSQL1 & " CLng(CVDate(Nz(TabSupplementenPerGebruiker.Einddat um))),"
strSQL1 = strSQL1 & " TabSupplementen.SupplementID, KliŽnten.Betaalwijze"
Set rstSupplWeek = db.OpenRecordset(strSQL1)
If Not rstSupplWeek.EOF Then
---
End if
So I add a line debug.Print strSQL1 at the end and paste the output in a new querySQL and I still am not amused ....
Aug 12 '06 #16

P: n/a
"Arno R" <ar***********@tiscali.nlwrote in message
news:44**********************@text.nova.planet.nl. ..

"Rick Brandt" <ri*********@hotmail.comschreef in bericht
news:KS*****************@newssvr25.news.prodigy.ne t...
ch************@gmail.com wrote:
>I completely disagree. The speed difference is neglible and the
self-documenting nature of SQL executed in code is a HUGE advantage over a
stored query def.
The 'self-documenting nature of SQL' is *not always* clearer than looking at
the
query-grid for me. [snip]
I was not referring so much to "what does the query do?" as "what is this query
for?" If I have SQL embedded in a code routine or function there is no question
about where the query is used and there is little question whether I can modify
it without adversely affecting some other part of the app. A saved querydef is
a "black box" in this regard.

Naming conventions and the design of the query should make obvious "what it
does", but after that you have do do a lot of digging.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 12 '06 #17

P: n/a
Paul H wrote:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAn dDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
I think there is not a general rule.

Stored queries have a precompiled query plan. But that plan may not be
efficient for stored Parameter queries. The nature of the Parameter
will be unknown to the query engine; it will guess what type and size
the Parameter will be but it cannot know. Because of this, in some
cases it may create an inefficient plan. So, substituting an SQL string
passed to OpenRecordset or Execute instead of using a stored Parameter
query may result in better performance (sometimes, maybe).
For a code jockey (I am one of these) the SQL string is tempting. We
don't leave the VB editor window. We debug.print our string to check
it. When we want to re-visit some operation we go to our module and
everything is right there. As we become comfortable with writing SQL
strings off the top of our head doing seems like nothing more than a
natural extension of our scripting. In some cases, we can force JOINs
which may or may not be more efficient than other operators. And, if we
move back and forth between JET and SQL-Server, we may have to do
nothing to use the same query strings. And of course, when we want to
port our application to ASP and SQL-Server all our strings are right
there.
In JET, I use the SQL string 99% of the time. But in MS-SQL Server I
tend to start with the SQL string, but later, when I try to optimize
what I am doing, I port the string to a Stored Procedure, View or User
Defined Function.
But MS-SQL Server T-SQL is a million times more powerful than
JET/Access SQL, allowing for looping, cursors, testing, explicit data
conversion etc. (I think this is the weakness of MS's current
encouragement of ODBC connections to MS-SQL Server; while these
capabilities can be used, there is no encouragement to do so, and in
fact, one can just pretend the whole thing is JET (I think) with not so
may problems. How often is this done? By the looks of the posts here, I
would say about 99.44%. SO to most of these persons who say, "I am
using MS-SQL Server" I would say, "Yeah, Yeah, Sure, Sure, Whatever!".
When creating something that makes use of these T-SQL strengths, it's
convenient to separate the logical parts into lines, as we do with most
code. Doing so in VBA, and then sending 500 lines off as a string is
not so convenient, IMO. Also creating procedures in most of the MS
utilities gives us a syntax check, which can be very helpful.

Aug 12 '06 #18

P: n/a
On Fri, 11 Aug 2006 19:24:31 +0100, "Paul H" <no****@nospam.com>
wrote:

I use mostly stored queries. The number of them doesn't bother me. A
good naming convention is important. And also I use groups
extensively, to keep logical sets of objects together. I've noticed
not many developers use those. I don't know why.

Inline sql is tolerable for 1-table queries, but not for multi-table
ones, where you just can't easily see what's going on.

Queries or stored procedures also give an extra level of indirection
which is sometimes nice if the database design is changing.

-Tom.

>I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDA ndDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul
Aug 12 '06 #19

P: n/a
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
I tend not to store any action queries. Just parameterised SELECT
queries. This is for my reporting needs. If I have a front end
database that does not have any reports and is just used for
collecting data (the reports will be in a different front end)
then I will not have any queries saved.
I generally don't use parameter queries, because the default UI is
not sufficient. And any of them with multiple values are annoying to
an end user. Secondly, you can preset the parameters only in code,
and that doesn't work when you're using it in a form or report.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 12 '06 #20

P: n/a
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:_e*******************@twister.nyroc.rr.com:
David W. Fenton wrote:
>rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:%l*******************@twister.nyroc.rr.com :
>>>You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.

Doesn't every developer work with all system and hidden objects
showing?

I have never looked over every developer's shoulder so I can't say
for sure.

I sure don't.
I have a hard time working at a user's machine because I have to
turn on display of hidden/system objects. And then I have to
remember to turn them off again when I'm done.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 12 '06 #21

P: n/a
Tom van Stiphout <no*************@cox.netwrote in
news:7n********************************@4ax.com:
Inline sql is tolerable for 1-table queries, but not for
multi-table ones, where you just can't easily see what's going on.
Huh? Why would that be? How can you not see what's going on when
you're looking directly at the SQL in the context in which it is
being used?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 12 '06 #22

P: n/a
Paul H wrote:
Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?
I prefer to code most of my queries because I don't secure my mdbs other
than to make them mde's and set the database window off in the startup.
I don't want some bozo holding the shift key and messing things up in
the query tab and then telling me my app doesn't work, while at the same
time saying "I didn't do anything". My experience is, especially with
users who are not particularly bright in the computer skills department,
that even if they've messed with something, they'll say they didn't. I
don't want that hassle so my queries are coded. Querydefs that I do
store are created and modified by code.

I do this for both my Oracle apps and for my Jet apps.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 12 '06 #23

P: n/a
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in news:_e8Dg.15415
$u********@twister.nyroc.rr.com:
David W. Fenton wrote:
>rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:%l*******************@twister.nyroc.rr.com :

>>>You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.


Doesn't every developer work with all system and hidden objects
showing?

I have never looked over every developer's shoulder so I can't say
for sure.

I sure don't.
Nor do I. Why would I want to see these things? What do they tell me?

--
Lyle Fairfield
Aug 12 '06 #24

P: n/a
On Sat, 12 Aug 2006 14:26:20 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

What I meant is that if the inline sql is simple (e.g. an insert into
a single table), it is easy to read and understand. If it is complex,
I rather see a descriptive name of the object (query or sproc) that
implements this complex sql and hides it, rather than a multiline
inline sql that can quickly become inaccessible.

-Tom.
>Tom van Stiphout <no*************@cox.netwrote in
news:7n********************************@4ax.com :
>Inline sql is tolerable for 1-table queries, but not for
multi-table ones, where you just can't easily see what's going on.

Huh? Why would that be? How can you not see what's going on when
you're looking directly at the SQL in the context in which it is
being used?
Aug 13 '06 #25

P: n/a
rkc
David W. Fenton wrote:
Tom van Stiphout <no*************@cox.netwrote in
news:7n********************************@4ax.com:

>>Inline sql is tolerable for 1-table queries, but not for
multi-table ones, where you just can't easily see what's going on.


Huh? Why would that be? How can you not see what's going on when
you're looking directly at the SQL in the context in which it is
being used?
Don't you ever use saved queries to build views that you
then use in other queries, or is that considered lame?

Aug 13 '06 #26

P: n/a
Hi David,

I don't, in actual, fact use 'Parameter queries' that much, just
queries that accept parameters, or critera. Whether the critera is
defined in a form or by prompting the user for values. Given the fact
that we may have had our wires crossed, can you please tell me how
parameter quries don't work for forms and reports, it is the only time
that they are used.

Nick

David W. Fenton wrote:
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
I tend not to store any action queries. Just parameterised SELECT
queries. This is for my reporting needs. If I have a front end
database that does not have any reports and is just used for
collecting data (the reports will be in a different front end)
then I will not have any queries saved.

I generally don't use parameter queries, because the default UI is
not sufficient. And any of them with multiple values are annoying to
an end user. Secondly, you can preset the parameters only in code,
and that doesn't work when you're using it in a form or report.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 13 '06 #27

P: n/a
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in
news:11*********************@p79g2000cwp.googlegro ups.com:
I don't, in actual, fact use 'Parameter queries' that much, just
queries that accept parameters, or critera. Whether the critera
is defined in a form or by prompting the user for values. Given
the fact that we may have had our wires crossed, can you please
tell me how parameter quries don't work for forms and reports, it
is the only time that they are used.
I don't know what the hell you're talking about, to be honest.

If the query has parameters defined, they SQL starts out with a list
of them and their data types. If not, then there are no parameters
defined.

I don't use those kinds of queries, saved or not, because they are
too inconvenient.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 14 '06 #28

P: n/a
Tom van Stiphout <no*************@cox.netwrote in
news:2b********************************@4ax.com:
On Sat, 12 Aug 2006 14:26:20 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>Tom van Stiphout <no*************@cox.netwrote in
news:7n********************************@4ax.co m:
>>Inline sql is tolerable for 1-table queries, but not for
multi-table ones, where you just can't easily see what's going
on.

Huh? Why would that be? How can you not see what's going on when
you're looking directly at the SQL in the context in which it is
being used?

What I meant is that if the inline sql is simple (e.g. an insert
into a single table), it is easy to read and understand. If it is
complex, I rather see a descriptive name of the object (query or
sproc) that implements this complex sql and hides it, rather than
a multiline inline sql that can quickly become inaccessible.
But then you've exported the problem of incomprehensibility to a
different location, from the code to the query tab of the database
window (and every dropdown list of saved queries).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 14 '06 #29

P: n/a
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:5c*******************@twister.nyroc.rr.com:
David W. Fenton wrote:
>Tom van Stiphout <no*************@cox.netwrote in
news:7n********************************@4ax.com :
>>>Inline sql is tolerable for 1-table queries, but not for
multi-table ones, where you just can't easily see what's going
on.

Huh? Why would that be? How can you not see what's going on when
you're looking directly at the SQL in the context in which it is
being used?

Don't you ever use saved queries to build views that you
then use in other queries, or is that considered lame?
I said that I use saved SELECT queries, yes.

But I use fewer of those for nested SQL than I used to. Instead
nowadays I'm more likely to use the "virtual table" syntax with
[SELECT ...]. As... That's absolutely necessary in some situations
where you need to use a WHERE clause on the nested query (especially
when the nested query is a GROUP BY query).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 14 '06 #30

P: n/a
David W. Fenton wrote:
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in
news:11*********************@p79g2000cwp.googlegro ups.com:
>>I don't, in actual, fact use 'Parameter queries' that much, just
queries that accept parameters, or critera.

I don't know what the hell you're talking about, to be honest.
Sounds to me like Nick is talking about query by form?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 15 '06 #31

This discussion thread is closed

Replies have been disabled for this discussion.