Prev: Search for a name with an apostrophe
Next: nesting queries - best practice?
From: Duane Hookom on 17 Jan 2010 20:02
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
> > > .
> > >
First  |  Prev  | 
Pages: 1 2
Prev: Search for a name with an apostrophe
Next: nesting queries - best practice?