473,225 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Which are better queries in code or stored queries?

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
3 2287
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
Thanks
This answers the question that I was trying to ask, not too successfully.

Neil
Nov 12 '05 #3
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: mmm, Pie | last post by:
I never ever thought about this, but recently I made a query in access so I can make a customer mailing list based on relational information and it was so easy to make the query and join tables and...
4
by: Support | last post by:
Hi, I want to know if I have changed a few records in my database using update / insert / delete methods, how can i later know which rows have been changed or modified ? I know the...
7
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the...
2
by: Roy | last post by:
Hey all, Here's a small VB codeblock that connects to a database and uses 2 SQL queries then forms a relation for a master/detail view on the aspx side: Private Sub Binddata(ByVal name As...
15
by: Rob Meade | last post by:
Hi all, I have a databse which I'm pulling the data from for my ASP page. I have 4 tables, Course, Feature, Objective, and PreRequisite. The last three all contain a course product code and a...
2
by: tulasikumar | last post by:
hi all, i have one doubt regarding queries and stored procedures.i am doing asp.net project,i had written both query based retriving and stored procudures.I want know about which one was giving...
6
by: jenipriya | last post by:
Hi all... its very urgent.. please........i m a beginner in oracle.... Anyone please help me wit dese codes i hv tried... and correct the errors... The table structures i hav Employee (EmpID,...
2
by: Andy B | last post by:
Is there an easy way to convert tableAdaptor queries into stored procs without messing up the dataTables in the dataSet or losing the queries themselves?
8
by: Frank Calahan | last post by:
I've been looking at LINQ and it seems very nice to be able to make queries in code, but I use stored procs for efficiency. If LINQ to SQL only works with SQL Server and stored procs are more...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.