making SQL the source for a report?

[Howard]

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.

 

[Duane Hookom]

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.

 

[Howard]

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

 

[Duane Hookom]

So, do you still have a question?

 

[Howard]

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]

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,...