Row source Best practice??

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

Since i am new to all this access stuff i have been tending to create new
queries everywhere i need them instead of just creating stand alone queries
(in the database window) and referring back to them...

For example every form that uses a employee name combobox will have its own
query for the rowsource (in the combos properties) rather than having a
employee names query in the database window and having all employee combos
reference that query.. the same goes for report data sources where each
report has its own query (in properties) rather than referencing a query in
the database window's Queries page..

I guess i really have three related questions:

1. will all of these queries eventually cause me problems or do they really
not matter??

2. Are there design rules or methods to keep queries efficient.

3. in a report is it better to just use the table as the reports record
source with dlookups for related data or to use ALL related tables in a query
in the reports datasource?

im sorry if the question is vague or off, i am just trying to keep Dumb from
running wild..

Thanks

Barry
 
Good questions.

Re #1 & #2:
No -- using saved queries won't cause you grief later. If you use a SQL
statement directly in the RecordSource of a form/report or the RowSource
property of a combo/listbox, Access creates a hidden query and saves it
anyway. Therefore there is no practical difference, and it's a matter of
style.

Advantages of using a saved query:
a) Single maintenance point. Particularly for combos, I like to create a
saved query and re-use it throughout the application (e.g. for other forms
where you also want a combo for clients also.) If you need to make a change
later (e.g. to sort all inactive clients to the bottom of the list), there's
only one query to change, and all your client combos get the same treatment.

b) May actually result in fewer saved queries (since they are reused.)

Disadvantage of saved query:
a) You have to manually copy the source queries also if you reuse the
form/report in another database.

b) There are more visible items to sort through on the Queries tab of the
Database Window / Navigation Pane.

Re #3
You *definitely* want to use a source query rather than have a bunch of
DLookup()s in your form/report. DLookups are slow and resource hungry. The
multi-table query will give you orders of magnitude better performance than
a table with a bunch of DLookups.
 
Thank you allen,

In regard to my question #2

I have come across talk somewhere about query performance. and SQL ordering
to keep queries efficient. maybe running a query with criteria first then
joining larger tables later or sorting after a using filtering criteria? i
dont really know what the difference is as the access "design View" doesnt
allow many options for the SQL arrangement.

If design view is good enough then design view is good enough..
Ill spend a little time on your tips page..

Thanks again for your earlier response

Barry
 
Allen Browne has largely provided fine answers to your questions,
but I'd like to add a number of points.

1. will all of these queries eventually cause me problems or do
they really not matter??
Click to expand...

It depends on your app. I used to make saved queries for everything,
on the theory that they were more efficiently optimized. But since
Jet saves optimization plans for recordsource and rowsource queries,
there was never much advantage there.

Where you have the same rowsource used on multiple forms, it does
make sense to use a saved query for that as long as there's never
any reason the instance of your combo box ever need to fork. I
recently screwed up a combo box on one form that used a saved query
that I edited for use in a combo box on a different form that needed
an altered rowsource. I simply forgot that the changes I was making
to the saved query were going to impact the other use of it.

So, you really have to keep track of your usage of shared QueryDefs,
because the sharing is a two-edged sword -- great when all uses are
identical, problematic when there needs to be variation.

In general, I don't create many shared queries any more. My most
recent complex project has a grand total of one combo box saved
QueryDef. That's about par for the course.
2. Are there design rules or methods to keep queries efficient.
Click to expand...

Saved or otherwise? In general, I use saved queries when I have a
common central set of data that gets used in multiple locations. I
then write an individual SQL string for each
form/report/combo/listbox that uses that saved query as a source.
This has worked quite well as it leaves the full control in the UI
object, but still allows a common core datasource that is edited in
one place.

Efficient queries in general is a huge question and deserves a
thread of its own, seems to me.
3. in a report is it better to just use the table as the reports
record source with dlookups for related data or to use ALL related
tables in a query in the reports datasource?
Click to expand...

As Allen said, DLookups are to be avoided. My philosophy of
DLookups() is that they should never be used in a SQL SELECT
statement. They only belong in the UI, as the Controlsource of a
control on a form or a report, or in code that is doing a single
DLookup() for some logical purpose (e.g., you might use a DLookup()
to see if you have existing records matching certain criteria).

A DLookup() in a SELECT statement will likely execute once per each
row returned (as long as it accepts row-based arguments, which is
the usual scenario), and that's just way too inefficient. If the
data you're looking for is accessible via data stored in the current
records in your SELECT statement, then you can join the data and get
to it directly, without needing to look up the values row-by-row.
With a join, Jet will pull the indexes for the joined tables, and
return in a batch the records that need to be joined. So, if you
join two tables, you're likely pulling two sets of data in large
batches. With a DLookup(), Jet pulls the set of data and then Access
executes the DLookup() for each row of your dataset. By the time
Access *starts* executing the first DLookup(), you could likely
already have the full resultset with the joined version.
 
They only belong in the UI, as the Controlsource of a
control on a form
Click to expand...

Another nice thing about a DLookup on a form is that they are
asynchronous. That is, you can have one (or any function call, for
that matter) as a column on a continuous form. You'll see the other
columns appear, then a moment later (while you see "calculating" in
the status bar) the calculated columns will appear. This means that
you can navigate, double-click, etc. without waiting for the
late-arriving values.

The trade-off is that you can't sort on a Controlsource column. If
you want to allow the user to sort on that column, you need to do it
down in the query and try to make it go as fast as possible.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
David

ThankYou for the info regarding my post..

Orginization (or my lack of) is deffinately a good reason to keep SQL
attached to what it is used for

Barry
 
Orginization (or my lack of) is deffinately a good reason to keep
SQL attached to what it is used for
Click to expand...

But that still means you're have a DRY problem, i.e., breaking the
"Don't Repeat Yourself" rule. In certain situations, that can be a
more serious drawback than the organizational problem.
 
Back
Top