From: Howard on
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
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
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
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
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
> > .
> >