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

Which are better queries in code or stored queries?

P: n/a
I'm a fairly new user of access & I've never had any training, so I'm
wondering if I'm doing the right thing here, or if it matter at all.
I'm building a database for room booking purposes and I'm trying to do
all record changing using queries. From what I can glean from this
(excellent!) group this is the right way to go about it. At present
what I'm doing is building & trouble-shooting the queries with
query-builder then using code to fire them off at the appropriate
time. It seems to me that this has several benefits: I can re-use
queries in different places, if a query isn't working correctly I can
trouble-shoot it directly, if I realize that I need to take account of
something else I can alter the query directly without having to worry
about all the places where I'm using it.
Is this the right thing to do? Or should I build the sql string in
code?
One thing I have noticed is that some queries only work in code. For
instance I have a delete query that works fine when I run it from
code, but if I try to run it using the query-builder it tells me that
it can't delete these records.
Any advice would be much apprieciated.

Ta

Neil
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I'm not sure, exactly, what you mean by "all record changing using queries".
But, if you mean using unbound forms and doing queries in code, that's
unlikely to be a concensus recommendation here.

If you simply mean using queries as the RecordSource of Forms and Reports
(and you wouldn't want to give users datasheet view of either queries or
tables in a developed application such as you describe, because there are
just too many undesirable things that the user can do accidentally), then
Queries or SQL is preferrable to using Tables. One reason is that you can
protect the data better if you implement security and give users access only
to queries that you define, not to tables directly.

Whether one uses saved Queries or SQL in the RecordSource is mostly a matter
of preference. Saved Queries are pre-processed by the Jet database engine to
create an execution plan; SQL in the RecordSource has a hidden query
automatically generated to take advantage of pre-processing. Both will
periodically have to be "replanned" as the database changes, but this is
done automatically, too, without your intervention being necessary. Neither
will benefit from the preplanning if different criteria are applied... the
RecordSource modified by code. BUT, you'll have to have _enormous_ tables to
see any difference looking at the Forms... at least in the multiple tens of
thousands of records.

Larry Linson
Microsoft Access MVP

"NeilAnderson" <ne***********@boroughmuir.edin.sch.uk> wrote in message
news:83**************************@posting.google.c om...
I'm a fairly new user of access & I've never had any training, so I'm
wondering if I'm doing the right thing here, or if it matter at all.
I'm building a database for room booking purposes and I'm trying to do
all record changing using queries. From what I can glean from this
(excellent!) group this is the right way to go about it. At present
what I'm doing is building & trouble-shooting the queries with
query-builder then using code to fire them off at the appropriate
time. It seems to me that this has several benefits: I can re-use
queries in different places, if a query isn't working correctly I can
trouble-shoot it directly, if I realize that I need to take account of
something else I can alter the query directly without having to worry
about all the places where I'm using it.
Is this the right thing to do? Or should I build the sql string in
code?
One thing I have noticed is that some queries only work in code. For
instance I have a delete query that works fine when I run it from
code, but if I try to run it using the query-builder it tells me that
it can't delete these records.
Any advice would be much apprieciated.

Ta

Neil

Nov 12 '05 #2

P: n/a
Thanks
This answers the question that I was trying to ask, not too successfully.

Neil
Nov 12 '05 #3

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in message news:<XB****************@nwrddc01.gnilink.net>...
I'm not sure, exactly, what you mean by "all record changing using queries".
But, if you mean using unbound forms and doing queries in code, that's
unlikely to be a concensus recommendation here.

If you simply mean using queries as the RecordSource of Forms and Reports
(and you wouldn't want to give users datasheet view of either queries or
tables in a developed application such as you describe, because there are
just too many undesirable things that the user can do accidentally), then
Queries or SQL is preferrable to using Tables. One reason is that you can
protect the data better if you implement security and give users access only
to queries that you define, not to tables directly.

Whether one uses saved Queries or SQL in the RecordSource is mostly a matter
of preference. Saved Queries are pre-processed by the Jet database engine to
create an execution plan; SQL in the RecordSource has a hidden query
automatically generated to take advantage of pre-processing. Both will
periodically have to be "replanned" as the database changes, but this is
done automatically, too, without your intervention being necessary. Neither
will benefit from the preplanning if different criteria are applied... the
RecordSource modified by code. BUT, you'll have to have _enormous_ tables to
see any difference looking at the Forms... at least in the multiple tens of
thousands of records.

Larry Linson
Microsoft Access MVP

"NeilAnderson" <ne***********@boroughmuir.edin.sch.uk> wrote in message
news:83**************************@posting.google.c om...
I'm a fairly new user of access & I've never had any training, so I'm
wondering if I'm doing the right thing here, or if it matter at all.
I'm building a database for room booking purposes and I'm trying to do
all record changing using queries. From what I can glean from this
(excellent!) group this is the right way to go about it. At present
what I'm doing is building & trouble-shooting the queries with
query-builder then using code to fire them off at the appropriate
time. It seems to me that this has several benefits: I can re-use
queries in different places, if a query isn't working correctly I can
trouble-shoot it directly, if I realize that I need to take account of
something else I can alter the query directly without having to worry
about all the places where I'm using it.
Is this the right thing to do? Or should I build the sql string in
code?
One thing I have noticed is that some queries only work in code. For
instance I have a delete query that works fine when I run it from
code, but if I try to run it using the query-builder it tells me that
it can't delete these records.
Any advice would be much apprieciated.

Ta

Neil


To All CDMA Participants Old and New:

I've recently accepted Jesus Christ as my personal savior. I want to
become a "new" Larry Linson. Therefore I'm repudiating this post and
all of my posts to CDMA over these many years. Those posts were the
work of the "old" Larry Linson. He was a liar and a deceiver.

To all of you who've supported me I can only say I'm sorry for letting
you down. To all of you whose work I've stolen I say I will never do
it again. And to those of you I've slandered or hurt in some way I
hope you'll find it in your heart to forgive me.

Respectfully Yours,
(The New) Larry Linson
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.