If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
making SQL the source for a report?
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. |
#2
|
|||
|
|||
making SQL the source for a report?
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. . |
#3
|
|||
|
|||
making SQL the source for a report?
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 |
#4
|
|||
|
|||
making SQL the source for a report?
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 . |
#5
|
|||
|
|||
making SQL the source for a report?
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 . |
#6
|
|||
|
|||
making SQL the source for a report?
You can't use Execute with a select query. Give up on hoping to somehow use
Execute to do anything that isn't an action query. If you think your queries aren't performing well then come back with some significant information about your query, indexes, joins, data types, sorting in the report, filtering of records for the report, subreport, page numbering,... -- Duane Hookom Microsoft Access MVP "Howard" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|