RecordSource Options

[hi there]

hello everyone,

i've just inherited a database from someone. after tinkering around a little bit i've noticed that the original developer used SQL (typed) for their report's recordsource. basically, they typed the actual SQL code in the RecordSource property. usually i base all of my reports on a dedicated query i develop and i guess this is kinda what the original creator is doing but could someone explain why someone would directly type SQL into the reports recordsource as opposed to basing a report on a query. are there any advantages to creating reports this way. any feedback on this will be greatly appreciated.

thanks all.

 

[yippie_ky_yay]

Hello,

I work with many shared databases and the reason why I try not to refer to a query is because I don't want someone else to delete it either by accident or because they aren't sure wether it's even being used or not. It also makes it easier for people in your situation who have to take over a db - there is no doubt for you how the report is grabbing the info (as opposed to a situation where someone bases a new report on an existing query, modifies it to their needs and not realizing that they are affecting the output of another report somewhere).

The advantages to keeping it seperate are that you can use it for several reports (1 change to effect many reports - bad if it goes wrong) and finally I've noticed that some queries are just too long to embed.

Hope that helps!

-Sean

 

[dcx693]

Perhaps the writer did not want more stored queries in the database than absolutely necessary. Storing SQL code in the recordsource means less clutter since the recordsource stays with the report.

Other than that, I can't think of a functional reason why it would be better to do this. Saved queries taking up less memory than SQL strings that need to be compiled at run-time.

Just for curiosity's sake, why don't you copy the recordsource string from the report, open a new query in SQL view, paste the SQL code in, and switch it to design view and perhaps you can figure out what the writer intended.

There are some queries that can't be represented properly in the QBE design view (like non-equi joins where the operator is >, <, <>, and Like), but as long as you can view them in SQL view, you can save them and base a report on them.

 

[hi there]

thanks for the responses sean and dcx693. i couldn't quite figure out why someone would do this, but i guess as with most things in Access there are multiple ways of doing things and multiple ways to justify them.

thanks again for your comments.

 

[hi there]

thanks for the response pat. i didn't know how, but somehow i expected that doing this was slower. as always yours and everyone elses responses clearly outlined the answer for me.

thanks