 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / March 2008qry vs sql code
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. 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.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Are there any benefits to using the sql code for a report as the > recordsource [quoted text clipped - 9 lines] > > thanks for all insight and help. 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. > Makes no difference whether you use a saved query or not. > Behind the scenes, Access saves a hidden query anyway. [quoted text clipped - 17 lines] > > > > thanks for all insight and help. 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. SignatureRob Ward > 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 [quoted text clipped - 27 lines] > > > > > > thanks for all insight and help. > 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. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com > 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 > 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 > ... 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. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. 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! > > ... again, why do it in vba as opposed to a query? > [quoted text clipped - 23 lines] > In general, we assign the RecordSource via VBA only when there is a special > need to do so. 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 SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Thanks, Allen. > [quoted text clipped - 46 lines] >> special >> need to do so. Thanks for your time Allen. I am sure i'll have some (possibly dumb) questions once i really get inside this. > Okay: crosstabs sometimes do have to do that. > [quoted text clipped - 53 lines] > >> special > >> need to do so. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.