Report Recordsource

[tman72]

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?

 

[hneal98]

One other thing to consider would be ease of maintaining. If you needed to add a field or change a table, which would be easier for you to work with?

 

[tman72]

Good point. I didn't even think of that, but I made this post to find out these types of issues.

Thanks!

 

[sdk]

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

 

[tman72]

Thanks for the input. Much appreciated!

 

[MichaelRed]

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