qry vs sql code

[patti]

Are there any benefits to using the sql code for a report as the recordsource
as opposed to the recordsource being a query?

I have inherited a multi-tentacled database that has no documentation. In
trying to troubleshoot reports, i have run across reports that use the code
as recordsource; then i copy and paste it into a query to see if that data is
valid, since the reports can get all willy-nilly with sorting and grouping.

thanks for all insight and help.

 

[Allen Browne]

Makes no difference whether you use a saved query or not.
Behind the scenes, Access saves a hidden query anyway.
Some people might find it easier if there are fewer queries showing in the
database window.

If you open the query in design view, and click the Build button (...)
beside the RecordSource property, it opens it in query design anyway.

 

[patti]

thanks allan. i was aware of the sql code behind the queries and didn't know
why one wouldn't just point to the query. From a troubleshooting angle, i
had to copy the recordsource code into a stand-alone query to see the data.

I also have a lot of recordset selection going on in vba w/ this db; again,
why do it in vba as opposed to a query? It's been a real bear tearing this db
apart to determine why reports are spitting out "wrong" data.

Thanks for sharing your experience and hard-earned expertise.

 

[Rob Ward]

All other things being equal, I personally think it's better to use queries
as the source for reports rather than code. I can think of at least three
reasons: 1) The queries are easier to build and debug, 2) there may well be
performance benefits as the query execution plan will be optimised and stored
- if the query is created from spliced code strings I don't think this
happens (though I stand to be corrected here); and 3), if the report takes a
long time to run, for debugging purposes you can temporarily change the
source query source to a 'make table', create a temporary table to base the
report on while you fix it, then switch the report record source back to the
original select query.

 

[Rick Brandt]

All other things being equal, I personally think it's better to use
queries as the source for reports rather than code. I can think of at
least three reasons: 1) The queries are easier to build and debug,

Why? SQL Statement RecordSources can be viewed in the same graphical query
builder and debugged there. They just do not show up as a saved query
object in the db window. Otherwise they are the same as any othger query.

2) there may well be performance benefits as the query execution plan
will be optimised and stored

No, see above.

- if the query is created from spliced
code strings I don't think this happens (though I stand to be
corrected here); and

I believe the OP was using the word "code" just to refer to SQL Statements
in the RecordSource property. You are correct that if the SQL was built in
VBA code and then assigned on-the-fly that there would be no pre-compiled
plan. On modern hardware it is the rare query where this makes a noticeable
difference however.

3), if the report takes a long time to run, for
debugging purposes you can temporarily change the source query
source to a 'make table', create a temporary table to base the
report on while you fix it, then switch the report record source back
to the original select query.

Can do the same thing with a SQL RecordSource if you just go to the query
designer view of it.

The biggest thing I like about SQL statements instead of saved queries is
that they are self-documenting as to where they are used. While it is easy
to see what saved query a report or form is using, it is not so easy to see
if anything else is using that same saved query so if you want to change it
you might break other stuff. With a SQL statement there is never any doubt
that it is ONLY used by the report or form where you find it.

 

[Tom Lake]

Rob Ward wrote:

The biggest thing I like about SQL statements instead of saved queries is that they
are self-documenting as to where they are used. While it is easy to see what saved
query a report or form is using, it is not so easy to see if anything else is using
that same saved query so if you want to change it you might break other stuff.
With a SQL statement there is never any doubt that it is ONLY used by the report or
form where you find it.

I like the saved queries just for that reason! I prefer reusable code so I don't
have to change the SQL statements in multiple places.

Tom Lake

 

[Allen Browne]

... again, why do it in vba as opposed to a query?

I'm not sure I understood that to be your question.

The report's RecordSource can be:
a) the name of a table
b) the name of a query
c) a SQL statement
d) a string variable in VBA, assigned in Report_Open.

If you are talking about (d), I don't think anyone has commented on this.

Developers generally use (d) where they are trying to build some flexibility
into a report that could not be there with a saved query. For example, you
may have a crosstab query, where the actual column names to be used at
runtime are not known by the developer at design time. He will therefore
open a recordset in code, grab the field names applicable to the filter to
be used for this run of the report, determine the field names, build the
PIVOT clause, assign the statement to the report's RecordSource, and assign
the fields to the ControlSource of the report.

This kind of dynamic assignment is certainly more involved than a saved
query or a static SQL statement used in the report's RecordSource. But is it
also enormously more powerful.

In general, we assign the RecordSource via VBA only when there is a special
need to do so.

 

[patti]

Thanks, Allen.

I am talking about a string variable in VBA, assigned in Report_Open.

A few of the "queries" (data selection process, if you will) within my vba
are crosstabs. Some are dynamic while others use the relative date M1, M2,
etc. There is a ton of machination going on and I am slowly dissecting it.

I will be doing a lot more research on crosstabs and the dynamic aspect.

I have checked out two links provided by Duane:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
http://www.tek-tips.com/faqs.cfm?fid=5466

To all who help, thanks!

 

[Allen Browne]

Okay: crosstabs sometimes do have to do that.

You probably don't need another unexplained example of code that does this,
but just to reassure you that whoever did that may have been doing the right
thing:
http://allenbrowne.com/temp/CrosstabExample.txt

 

[patti]

Thanks for your time Allen.

I am sure i'll have some (possibly dumb) questions once i really get inside
this.

 

[Larry Linson]

I like the saved queries just for that reason! I prefer
reusable code so I don't have to change the SQL
statements in multiple places.

I almost never encounter a situation where there is a real need to use the
same query -- and, at times in the past, have tried that, then found that I
had to change either the form or the report that used the query but not the
other.

As far as SQL for RecordSource, as Allen has already pointed out, Access
realizes that SQL in a RecordSource is likely to be used repeatedly, so
"behind the scenes" creates a query from the SQL in the RecordSource and
saves that so it is optimized / compiled / prepared as saved queries are.
If you query the appropriate system table, you'll see those Access-generated
saved queries, the names of which (always/usually) begin with ~.

Where you may find a difference in performance is in SQL created in code and
executed from code... but you'd need a lot of records for the difference to
be discernable on the user's screen. I often use SQL executed from VBA code
against tables with tens of thousands of records, and have sometimes
compared against a saved query, and could see no difference. Thus, I'd say
the "lot of records" would have to be in the hundreds of thousands for it to
make a really discernable difference.

Larry Linson
Microsoft Office Access MVP