473,322 Members | 1,714 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,322 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 2293
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.