making SQL the source for a report?
in [Queries]
|
From: Howard on 14 Jan 2010 15:01 I am currently rewriting a big database from using things like DoCmd.OpenReport "Rpt_Y10_A2C_movement_by_student", acViewPreview, where the report's data source is a saved query, to something like Set db = CurrentDb strSQL = SQL_Y10_A2C_movement_by_student 'generate sql from a function db.Execute strSQL, dbFailOnError 'run sql Set db = Nothing where the SQL (copied from the original query) is held in a function eg Private Function SQL_Qry_Y10_A2C_movement_by_student() As String Dim s As String s = "" s = s & "SELECT Y10_ALL_Est_num_A2C_1.Adno, " s = s & Blah Blah Blah SQL_Qry_Y10_A2C_movement_by_student = s End Function This is to speed it up and prevent warnings when using action queries. Qn Can I somehow make a report's data source be the result of the db.execute's SQL? Or do I need to change all my select SQLs to 'make table' ones and base my reports on the resulting table. This seems a bit unnecessary ans maybe will be just as slow.
From: Duane Hookom on 14 Jan 2010 15:59 You can set the Record Source of a report in it's On Open event or possibly set the SQL property of a saved query prior to opening the report. Execute SQL should only run action queries like DELETE, INSERT, UPDATE, etc. I'm not sure what limitation you are facing that you can't just use a where condition in the DoCmd.OpenReport method or set a criteria in your report's record source. -- Duane Hookom Microsoft Access MVP "Howard" wrote: > I am currently rewriting a big database from using things like > > DoCmd.OpenReport "Rpt_Y10_A2C_movement_by_student", acViewPreview, > > where the report's data source is a saved query, to something like > > Set db = CurrentDb > strSQL = SQL_Y10_A2C_movement_by_student 'generate sql from a function > db.Execute strSQL, dbFailOnError 'run sql > Set db = Nothing > > where the SQL (copied from the original query) is held in a function > > eg > Private Function SQL_Qry_Y10_A2C_movement_by_student() As String > Dim s As String > s = "" > s = s & "SELECT Y10_ALL_Est_num_A2C_1.Adno, " > s = s & Blah Blah Blah > SQL_Qry_Y10_A2C_movement_by_student = s > End Function > > This is to speed it up and prevent warnings when using action queries. > > Qn > Can I somehow make a report's data source be the result of the > db.execute's SQL? Or do I need to change all my select SQLs to 'make > table' ones and base my reports on the resulting table. This seems a bit > unnecessary ans maybe will be just as slow. > > > . >
From: Howard on 14 Jan 2010 17:10 Duane Hookom wrote: > You can set the Record Source of a report in it's On Open event or possibly > set the SQL property of a saved query prior to opening the report. > > Execute SQL should only run action queries like DELETE, INSERT, UPDATE, etc. > > I'm not sure what limitation you are facing that you can't just use a where > condition in the DoCmd.OpenReport method or set a criteria in your report's > record source. " Execute SQL should only run action queries like DELETE, INSERT, UPDATE, etc. " - Ah, I did wonder about the ethics of using this command. Thank you No limitation. I 'discovered' db.execute as a means of preventing the warning messages about you are about to add' etc but then became very impressed with the increased execution speed and the ease with which I could dynamicaly build my SQL, so I set about changing my SELECT and calculation queries to db.execte as well. I then became stumped about how to link them to thier reports. I have re-coded some of the larger crosstabs to 'make tables' prior to using them with a dynamically sized report and they run a lot faster than calling the saved query version. I also have a big access database that does nothing but process tables from a MS SQL backend (to which I don't have write access) and I am currently trying to port that to Delphi so I can generate a stand alone executable. There it would be an advantage to have everything hard coded in SQL as I would have nowhere to store any saved queries. Howard
From: Duane Hookom on 15 Jan 2010 10:56 So, do you still have a question? -- Duane Hookom Microsoft Access MVP "Howard" wrote: > Duane Hookom wrote: > > You can set the Record Source of a report in it's On Open event or possibly > > set the SQL property of a saved query prior to opening the report. > > > > Execute SQL should only run action queries like DELETE, INSERT, UPDATE, etc. > > > > I'm not sure what limitation you are facing that you can't just use a where > > condition in the DoCmd.OpenReport method or set a criteria in your report's > > record source. > > " Execute SQL should only run action queries like DELETE, INSERT, > UPDATE, etc. " - Ah, I did wonder about the ethics of using this > command. Thank you > > No limitation. I 'discovered' db.execute as a means of preventing the > warning messages about you are about to add' etc but then became very > impressed with the increased execution speed and the ease with which I > could dynamicaly build my SQL, so I set about changing my SELECT and > calculation queries to db.execte as well. I then became stumped about > how to link them to thier reports. I have re-coded some of the larger > crosstabs to 'make tables' prior to using them with a dynamically sized > report and they run a lot faster than calling the saved query version. > > I also have a big access database that does nothing but process tables > from a MS SQL backend (to which I don't have write access) and I am > currently trying to port that to Delphi so I can generate a stand alone > executable. There it would be an advantage to have everything hard coded > in SQL as I would have nowhere to store any saved queries. > > Howard > . >
From: Howard on 17 Jan 2010 07:05 Well, yes, You say db.execute 'should' only run action queries. It seems so much faster that docmd. Is there form of coding that will allow me to use it execute a select query and obtain a reference to the result set it creates so that a report can be based upon the returned data or the returned data be used in a futher bit of sql? Howard "Duane Hookom" wrote: > So, do you still have a question? > -- > Duane Hookom > Microsoft Access MVP > > > "Howard" wrote: > > > Duane Hookom wrote: > > > You can set the Record Source of a report in it's On Open event or possibly > > > set the SQL property of a saved query prior to opening the report. > > > > > > Execute SQL should only run action queries like DELETE, INSERT, UPDATE, etc. > > > > > > I'm not sure what limitation you are facing that you can't just use a where > > > condition in the DoCmd.OpenReport method or set a criteria in your report's > > > record source. > > > > " Execute SQL should only run action queries like DELETE, INSERT, > > UPDATE, etc. " - Ah, I did wonder about the ethics of using this > > command. Thank you > > > > No limitation. I 'discovered' db.execute as a means of preventing the > > warning messages about you are about to add' etc but then became very > > impressed with the increased execution speed and the ease with which I > > could dynamicaly build my SQL, so I set about changing my SELECT and > > calculation queries to db.execte as well. I then became stumped about > > how to link them to thier reports. I have re-coded some of the larger > > crosstabs to 'make tables' prior to using them with a dynamically sized > > report and they run a lot faster than calling the saved query version. > > > > I also have a big access database that does nothing but process tables > > from a MS SQL backend (to which I don't have write access) and I am > > currently trying to port that to Delphi so I can generate a stand alone > > executable. There it would be an advantage to have everything hard coded > > in SQL as I would have nowhere to store any saved queries. > > > > Howard > > . > >
|
Next
|
Last
Pages: 1 2 Prev: Search for a name with an apostrophe Next: nesting queries - best practice? |