Report runs query three times

  • Thread starter Thread starter Phil Bewig
  • Start date Start date
P

Phil Bewig

I have a query that runs properly. The query is a
pass-through query that runs a complicated sql select
statement involving ten unions. It takes about twenty
minutes to return results. The odbc driver requires
me to login once each time I run the query.

I also have a report based on that query. The report
is simple. A break on one field causes a new page to
be started, with labels, fields from the query, and a
few calculated fields in the page header. A break on
another field causes a blank line and prints two fields
from the query. The detail line prints fields from the
query, with no calculations. There are no sub-totals
or totals in the report.

Every time I run the report the odbc driver requires
me to login three times, at twenty minute intervals;
thus, it takes one hour to run the report. At the end
of this time the report is correct, and the data
matches the query. This behavior occurs in my normal
environment of Windows 98, Access 2000, and Oracle 9;
I have the same problem on a Windows 2000 machine, and
have tested both the Microsoft and Oracle odbc drivers
with the same result.

I am certain there is some error in the definition of
the report that is causing this behavior. But I can't
think of any reason that the query would run three
times. Does anyone know what problems may cause this
symptom?

By the way, what is the comment convention for Oracle
pass-through queries? Neither /* comment */ nor
-- comment works.

Many thanks for your kind assistance.

Phil
 
Phil,

My experience with ODBC does not include Oracle, so I can't offer
anything specific to your environment. I'd agree that it is something
in the report (perhaps you are using 'Page X of Y Pages' -- that would
cause a two-pass report which might run the query twice), but I can't
see from your description just what.

What I can suggest is "generic":

-- carefully examine the passthrough query with
your local Oracle DBA/guru and see if you can
improve it

-- use it to create a local Access table of data
for the Report and Report on that (see MVP Tony
Toews' site http://www.granite.ab.ca/accsmster.htm
for how to create a temporary database to contain
temporary tables and avoid the bloat often
associated with temporary tables).

These "workarounds" may help somewhat until you get a response from
someone with specific Access-ODBC-Oracle experience. And, you might
crosspost this question to the microsoft.public.access.odbcclientsvr
newsgroup, too -- I don't usually suggest crossposting but in this
instance it may be a good thing.

Larry Linson
Microsoft Access MVP
 
(e-mail address removed) (Phil Bewig) wrote in message
Returning to work after the long weekend, the report now
works properly, calling the query only once and completing
in twenty minutes. I didn't do anything to change the
report, and don't know why the report behavior changed.

Thanks to those who replied on the newsgroup and privately.

Phil
 
Back
Top