A.Q said:
- The DB server is Oracle.
I've done no work with Oracle as the Server DB, so I won't have any
"Oracle-specific" tricks.
and I use ODBC to connect to the server. And use DAO
I've done a good deal of work with Access clients, using DAO and ODBC.
- I used form with listbox -multiselect option - to display 6 fields -id,
name, creator, date... etc (7-8K records).
I can't imagine using one listbox to display multiple fields. I can imagine
using a continuous forms view Form with multiple Controls, one per Field.
It's a long list so I have 3 text
box 1 check box and 1 combobox to do the filter. User have the option to
filter to the site they know or want. or just click the site(s) in listbox.
if they click the listbox then "Preview report" button is enabled. then if
they click "Preview report" button, the report will display. The reasons I
have to do 6 subreports are we need to have all the multi records. and each
record have to display to 1 page or 2 max, each report have to be in a new
page... (The whole application I have to link at least 40 tables together & 2
views).
My main query is more then 10pages and it use for main report. each
subreport have 1 query. and relative by siteid.
I also have never come close to using a Query whose SQL runs for anywhere
_near_ ten pages. You are doing a lot of work.
In my experience (though never with Oracle), the ODBC drivers we used seemed
to do a reasonable job of not altering the SQL too drastically before
sending it on. The ODBC drivers we used were from InterSolv (which I
understand has now been acquired or changed its name). On the other hand, in
older versions of Access, the Jet database engine might decide that the
query was too complex, and retrieve all or most of the information to do the
selection on the user's machine. That is when we resorted to creating Views
to force that work to be done on the server.
the code under "preview report" button is
Set db = CurrentDb
Set qd = db.QueryDefs("SelectionALL")
qd.SQL = GetSQL()
DoCmd.OpenReport "SelectionALL", acViewPreview
and there is a function GetSQL, which will go thru the listbox and pick
the
site id and put in an variant name sList (can be 1 siteid or 2,3.. siteid)
to
make siteid IN (" & sList & ") " like that.
Ok, those are what I did, and work great. but slow...
Well, since my first time with VBA and access: I'm not understand how it
works, so
The fact that your query is doing a lot of work concerns me. That means that
you are doing much different kind of applications than I have ever done. I
had a colleague whose work required similarly complex SQL and, when we
discussed what he was doing, yes, our appllications were very different. So,
I know there are cases where that is required. I also know there are cases
where the database design itself forces complexity that could be avoided.
-I was try to make the old queries for subreport, define them as pass-thru
queries, then, create new queries that select from those pass-thru. But it
seem not working when I call the main report.
When you use Reports embedded in a subreport control and the
LinkMasterFields and LinkChildFields to select, you are doing "filtering"...
reading more records, but displaying only those that match. This, too, will
be less efficient. Unfortunately, you can't reset the RecordSource for the
Report embedded in the Subreport for each Record. But, as I pointed out, the
Subreport functionality is such that filtering is done locally, on the
user's machine.
I am not aware that you cannot use Pass-Thru Queries as the RecordSource of
a Report embedded in a Subreport Control. I haven't tried it, and haven't
tried using a Pass-Thru Query as the data source of a local Query... if the
local query is where the record selection is being done, then all the data
may have had to be transferred across the network already.
-How do I create view from the application?
I've always created Views using the functionality of the Server (in most
cases where I was doing so, that was Informix). As far as I know, the only
situation where you might be able to create a View from the Access client
application would be in an Access ADP/ADE, which can only be used with
Microsoft SQL Server, not any other server DB.
-Now I'm thinking of passing the sList to all the queries of each subreport
(?) Haven't reduce the speed yet... at least 30-45seconds for 1 site!
One question that I have not asked is this... "slow" compared to _what_? Do
you have an example of similar amounts of work being done much faster using
a different front-end? Can you execute your SQL directly from an Oracle UI
to get a sense of timing -- that is, whether the delay is Oracle or whether
it is in the interface between Oracle and Access? Do you have recording
software that is logging the information passed between Jet/ODBC and Oracle?
That is... retrieve a record, then retrieve all the records that would be on
the associated six subforms.
The only way to really be successful at speeding up the application is to
know where the delay occurs and what may be the cause, so you can address
them. Otherwise, you may "play" with factors that really have very little
influence on the response time.
But, because the retrieval can be forced to be in the server, it certainly
would be worthwhile to look at doing the report _without_ subreports, using
grouping on what are now your main records, and detail on the related
records.
Best of luck with your project.
Larry Linson
Microsoft Access MVP
