Hi Pete

Thanks for the response.

Looking at the queries used in the reports I think the problem lies in the
joining - ie there aren't any table joins as the joins exist between
sub-queries. So please correct me if I have got it wrong on this : for each
subquery, the entire tables contents are returned for comparison in the
parent query, therefore the database is queried many times, rather than once
when doing joining on the tables. I imagine that this will slow things down.

...

Did a comparison after starting this email - cut 30s off a 42s query by
changing the joining. Hence the "well-written" qualifier in your response.

Thanks again.

Mark

-----Original Message-----
From: Peter N. Kipe [mailto:pkipe@c...]
Sent: Tuesday, February 18, 2003 2:33 PM
To: professional vb
Subject: [pro_vb] RE: Creating Access reports through VB


In a very large-scale project completed a couple of years ago, we had a
requirement for roughly 150 different reports, many of which were based on
extremely complex SQL.  After trying several different approaches, we found
that the best performance was obtained with well-written SQL views or stored
procedures, and Crystal Reports (8.0 at the time).

In developing a new report, we determined what the result set needed to
contain, then we gave that spec to a database specialist who developed
either a view or a stored procedure, depending upon the complexity.
Meanwhile, we developed the CR8 report with a dummy view that returned a few
records containing the correct fields.  The project was developed in VB6/SQL
Server 7.0, and used ODBC.

I think the answer to your first question will differ based upon the exact
environment you're in -- ADO, ODBC, RDO, etc.

Pete

-----Original Message-----
From: Mark Warner [mailto:margarete@a...]
Sent: Tuesday, February 18, 2003 4:22 AM
To: professional vb
Subject: [pro_vb] Creating Access reports through VB


Good day List

The story so far:
Some MS Access reports exist within an mdb that is linked to a remote Oracle
DB. Queries are run on this DB, creating reports. The problem is that
running these queries across the network is very slow at the best of times.
The person who runs these reports currently does not have access to the
server except through a DSN connection.

First of all : Can someone explain to me what happens under the hood when
you run a query that exists only in the mdb, from a remote machine? How does
the select request get handled? (all the queries are "select... from...")

Secondly : Can someone suggest a way of speeding up the turnaround time, or
point me in the right direction for some literature in this regard?

What I thought of doing was creating a VB app (that would create the reports
in HTML format), this app would exist on the same server as the DB, then
return the report as HTML to the client through an ASP script. I'm
investigating (read bashing my head against a wall) using the DoCmd for
this. If someone has done this before or has a better method of doing it
please help!!

Any assistance in this regard would be greatly appreciated.

Thanks
Mark

______________________________________________
"The information contained in this communication is confidential and
may be legally privileged.  It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it.  If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action
in reliance of the contents of this information is strictly prohibited
and may be unlawful.  Absa is liable neither for the proper, complete
transmission of the information contained in this communication, nor
for any delay in its receipt, nor for the assurance that it is
virus-free."




______________________________________________
"The information contained in this communication is confidential and
may be legally privileged.  It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to
receive it.  If you are not the intended recipient you are hereby
notified that any disclosure, copying, distribution or taking action
in reliance of the contents of this information is strictly prohibited
and may be unlawful.  Absa is liable neither for the proper, complete
transmission of the information contained in this communication, nor 
for any delay in its receipt, nor for the assurance that it is 
virus-free."