RE: There must be a better way...

["Mark Villa" <markvilla@xxxxxxxxxxx>]

~~~Steven Donnellan

~~~I have a user who has a Library containing data files she
~~~uses ODBC to get
~~~in to Microsoft Access for analysis.

How does she do it? Leave the data on the AS/400 or transfer it first to
Access?


~~~aren't small(!) and at times the ODBC job takes up a lot of
~~~wallop on the
~~~AS/400.
Sounds like live queries instead of pulling data over.
did she copy the structure keys? A logical may help by creating the key
sequence she is asking for. It sounds like it can be somewhat predictable.

Although I have not checked the difference, configuring the ODBC connection
type for READ-ONLY should improve time. I always do that for safety. Looking
at the MS Access SQL view of what is being asked sometimes helps see how
inefficient the request is. Usually, selecting the set from the AS/400 while
pointing these results to a destination table in Access, for further
analysis, is the way to go. The closer you get to a million records the
worse it is.
If more than one table per query on the ACCESS side is involved, the records
get pulled there anyway, that may be the reason it is "noticeable". If
during the course of "analysis" the record sets get traversed more than
once, it make sense to put them into access first, if performance is
"noticeable".

It is easy to make a case for "doing on the AS/400" altogether, but many
folks do what you are doing.

When we upgraded to RISC, performance was like night and day. I was not
reasonable for us on CISC to use MS access driven solutions with an active
connection. If you are using AS/400 RISC, something fundamental may be
wrong.

This example may help explain what I think: if you have a big database that
can be separated by states (for example) do it with logical files and use
access to connect to these, then recombine them later if needed. It takes
less time to do 50 smaller groups than 1 big group, especially if it nears
the limit.

Our alternative prior to RISC was Typing datasets to PC's for crunching.


Mark Villa in Charleston SC