Is it better to build a query and use that for a recordsource of a report or to set the recordsource using an SQL statement in code in the report's Open event? I use some reports based on queries and others use and SQL statements. Seems they work about the same speed. What are some other things to consider?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Report Recordsource
- Thread starter tman72
- Start date
- Thread starter
- #3
Hi
my rudimentary understanding is that the Queries are precompiled - unlike embeded sql, and that therefore there will be at least a slight drop in performance.
Personally, I prefer to use sql as the source, but acknowledge that data mtce can become a bigger issue (although there are ways to limit this), and it does create problems when using similar datasets for report outputs (to make sure the numbers match across reports you want a common source). The advantage is I know where the sql is, that if I change it there it doesn't have side effects to other datasets, and that I don't have to come up with another variant query name.
Cheers
S
my rudimentary understanding is that the Queries are precompiled - unlike embeded sql, and that therefore there will be at least a slight drop in performance.
Personally, I prefer to use sql as the source, but acknowledge that data mtce can become a bigger issue (although there are ways to limit this), and it does create problems when using similar datasets for report outputs (to make sure the numbers match across reports you want a common source). The advantage is I know where the sql is, that if I change it there it doesn't have side effects to other datasets, and that I don't have to come up with another variant query name.
Cheers
S
- Thread starter
- #5
MichaelRed
Programmer
re the ease of change, it is quite common to "trap" the SQL, copy it, paste it into the query builder, do the mods, copy i back to the original source (embedded SQL string in the on open event). this DOES require that you re-work any sections which employ parameters.
it can be somewhat difficult to do this in subreports, as Ms. A. doesn't like to change hte recordsource after printing /formatting has started. so reports with sorting and grouping options set essientially loop through the sort/group sections and the subreports may be "opened" as often as the sorting/grouping parameters dictate.
MichaelRed
it can be somewhat difficult to do this in subreports, as Ms. A. doesn't like to change hte recordsource after printing /formatting has started. so reports with sorting and grouping options set essientially loop through the sort/group sections and the subreports may be "opened" as often as the sorting/grouping parameters dictate.
MichaelRed
- Status
Similar threads
- Locked
- Question
