Another opinion.

We do virtually the same thing that Dan mentions below. Each morning (around
3am local time) we copy the contents of various System 21 tables to our SQL
server and have both Access and VB queries interrogate the data to produce
various reports.

For example, we have complete replaced the System 21 Sales Analysis module
with one of our own and run it across our WAN. Very fast, very thin client
and all users are quite happy.

A comment on the speed of the Access queries. On occassion, we have had to
do live queries into System 21 and have found the response time to be
dependent on what other jobs are running on our AS/400 at the time. A year
or so ago, we upgraded the processor on our box and found these interactive
queries to run faster. We are planning another upgraded in Sept and believe
that will speed things up even more.

Using VB works similiarly to Access in that it uses the ODBC connection to
grab the data and then you can do what you wish. If you want more
information, feel free to contact me directly. I would be happy to talk with
you about it.

Good luck!

Stuart Sandler
Director of Information Systems
Krebs Engineers
(520) 744 - 5601

-----Original Message-----
From: Dan Thomas []
Sent: Wednesday, June 19, 2002 8:42 AM
To: ''
Subject: RE: SYS21 - System 21 access from Vb

I've successfully used MS Access to write hundreds of queries and reports
against JBA data.  You link Access tables to data on the AS/400 using an
ODBC connection.  Data continues to reside on the AS/400, but can be used
like local Access tables.  Access has a facility for defining relationships
between files and storing those relationships, or you can do it individually
for each query.  You simply drag arrows between corresponding fields to do
the "chain".  I'm not that familiar with VB, but I would expect it uses
similar technologies.

We've found that performance varies dramatically from query to query and
probably depends upon your datasets and how the AS/400 query optimizer is
working.   One very nice thing about Access is that a data source for a
query can be another query or a query combined with a table.  I've often
been able to dramatically improve the performance of a query by breaking it
into multiple sub queries chained together.  You can easily have the queries
prompt for runtime data, such as starting/ending customer#, invoice date,
etc.  One nice thing about Access queries is they can easily be extracted
into Excel with no programming.

You have the option in Access of writing SQL statements that run on the
AS/400 instead of letting Access run the normal way.  There are many pundits
of this approach, but I personally have found it unnecessary.  One
performance issue to consider is the ODBC AS/400 access runs under the
interactive subsystem.  You have to be careful with this.  I expect some
expert users have figured out a solution for this.

Overall, I would say Access is a very good tool for developing queries.
Personally, I prefer it to any other tool I've used including DB2 SQL Query
Manager, IQ Objects and Crystal Reports.  I don't particularly care for the
Access report design tool.  In our environment, almost everything we do is a
query that is then extracted into Excel.  If I was primarily developing
formatted reports for printing, I would probably use Crystal Reports.

In our environment, we use DataMirror to replicate our JBA AS/400 data to MS
SQLServer to create a "datamart".  We use the datamart for our client web
information portal since we get dramatically better performance accessing
data from SQLServer than we do the AS/400.  Acess queries against this
datamart are also much faster than directly hitting the AS/400.

I hope that much of what I've written about Access applies to your VB

Dan Thomas
Director of Information Technology
4500 Progress Blvd
Louisville, KY  40218
(502) 318-1208

-----Original Message-----
From: Kincer, Alan []
Sent: Wednesday, June 19, 2002 11:08 AM
To: ''
Subject: RE: SYS21 - System 21 access from Vb

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
[ Picked text/plain from multipart/alternative ]

I will know in the future now to say "No vendor SOLICITATIONS" :) Of course
replies are always appreciated.

My response would have been to do it in FoxPro. Blazingly fast access of 400
data compared to VB, especially now with version 7.

-----Original Message-----
From: Randy Rasp []
Sent: Wednesday, June 19, 2002 11:27 AM
Subject: Re: SYS21 - System 21 access from Vb

[ Converted text/html to text/plain ]
Darn!  No vendor replies?  I was going to tell you how to do it.   : )

Randy Rasp, CCP
Dallas Branch Manager
Information Alternatives
PO Box 168386
Irving, TX  75016-8386
Phone:  972-753-1266
Fax:  513-936-3710 wrote: -----
To: <>
Date: 06/19/2002 09:22AM
Subject: SYS21 - System 21 access from Vb

We are looking to write an inquiry program in Vb which needs to get somedata
from Inventory.  Basically the equivalent of doing a couple of"Chain's" in
RPG.  Has anyone done this before, did it work, and whatmethod of accessing
the data did you use?Thanks in advance,NeilPS  No vendor replies
This transmission and any attachments are confidential and are intended
solely for the named addressee (s). If you are not the addressee, please do
not read, copy, use or disclose this transmission and please notify us
immediately by telephone on+44 (0) 1670 594848 or by reply.  Please then
delete this transmission from your system.Although we have taken steps to
ensure that this email and attachments are free from viruses, we advise that
in keeping with good computing practice the recipient must ensure that they
in fact are virus free.No contracts may be concluded on behalf of Fone
Logistics LTD by means of email
communications._______________________________________________This is the
GEAC/JBA System 21 Users (JBAUSERS-L) mailing listTo post a message email:
JBAUSERS-L@midrange.comTo subscribe, unsubscribe, or change list
email: JBAUSERS-L-request@midrange.comBefore posting, please take a moment
to review the archivesat[2].


This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email:
To subscribe, unsubscribe, or change list options,
or email:
Before posting, please take a moment to review the archives
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email:
To subscribe, unsubscribe, or change list options,
or email:
Before posting, please take a moment to review the archives
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email:
To subscribe, unsubscribe, or change list options,
or email:
Before posting, please take a moment to review the archives

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.