Query or SQL for record source

[mhifd009]

I just wondered what your thoughts were on the best way to reference the 'record source' within a form or report, i am finding that it is best to use the SQL rather than the actual named query (a close version of the query can then be used without building up query listing).

Im I doing this the best way ??

[ADezii]

I just wondered what your thoughts were on the best way to reference the 'record source' within a form or report, i am finding that it is best to use the SQL rather than the actual named query (a close version of the query can then be used without building up query listing).

Im I doing this the best way ??

To the best of my knowledge, it is more efficient to use Stored Querys rather than SQL, since they have already been Optimized and the most efficient Execution Path has already been chosen. Don't take this as Bible truth, however, I'd wait until some of the SQL Gang chime in.

[Jim Doherty]

I just wondered what your thoughts were on the best way to reference the 'record source' within a form or report, i am finding that it is best to use the SQL rather than the actual named query (a close version of the query can then be used without building up query listing).

Im I doing this the best way ??

A saved object is going to be better as the query objects SQL is stored readily available optimised to the engine whereas a string has to be read first, determined/verified then run. This was true of Access version 2 subsequent versions optimise embedded SQL statements in forms and reports as though they were saved queries.

As ever its a matter of 'performance versus overhead'. You have identified what suits you. You can get a query window the size of a house if everything is placed in there sometimes so to that extent I agree with you. Provided you know where it is then personally I don't think it really matters.

It is always nice to have some form of discipline for yourself and for the benefit of others who might have to deal with your application after you but I find it is largely application driven.

Sometimes you need dynamic SQL in code which lets face it, gets saved to 'nowhere readily visible to the naked eye' sometimes, except in the code window and even then its very often built interspersed with loops and other fancy sequences in order to make the program function......S o no! IMHO opinion I don't think one can be too purist about it.

There are various other techniques as well, one of which is to store the SQL in a table of your own and pull it back yourself referenced by an ID or some other mechanism to suit you. The logic here being you ar not padding out the query window and you have a central repository of SQL to call on so you know where it is (all very well and good until that particular table corrupts and then those entrepenaurs start thinking hmmm I wish I'd written those to external text files now instead of that)

If you have a complex query or a main query used often then in my book you are much better off saving that to the query window for performance reasons. For all other types of retrievals ie small dynamics where datasets are small then you hardly notice any difference.

On your specific observation one could say "well if you save it to structure of forms or reports and you need to alter the specifics of the SQL slightly then each report has to be revisited of if if you have converted to MDE you are stuffed because the SQL is not editable then whereas you could amend a saved query" and so on. Swings and roundabouts? happy to live with?, benefits versus restrictions? I leave it to you.

Regards

Jim :)