getting data from AS400

[geebee]

I am working on a front end in MS Access, but ALL information is currently
entered and stored in an AS400 mainframe at this point. So users get the
data by us using Showcase, having it export data into a .csv file, and then
us distributing a .xls to users. There HAS to be a better way. I would like
to have users use an MS Access front end to somehow query and view the data
in the AS400 mainframe. How can this be achieved?

Thanks in advance,
geebee

 

[Alex Dybenko]

Try to search Microsoft .com for AS400 - I believe they had something for
this, but don't remember product name.

 

[Ron Weiner]

IBM has an ODBC driver for the AS 400 that when installed on a workstation
makes it possible for Access to directly Link / Import tables. From there
you are in semi-fat city. My experience has been, well less than totally
satisfactory with the IBM drivers. Force your AS400 Guys and Gals to get
the latest patches and updates for the ODBC drivers before even making an
attempt. Once the latest drivers were installed we were able to get
reliable read access to the AS400 data that we needed for our project. I do
not have any experience with writing data back to the AS400, and would
caution you to have a full and open discussion with your AS400 caretakers,
minders, feeders, polishers (whatever the heck they are) before even
thinking about writing data back to that bad boy. I assure you that your
name will become er... "a well known one" in your enterprise should you
screw the pooch on the AS400.

OBTW you will need to install and configure the ODBC driver on each
workstation that needs to have access to the data. You might want to
consider using a overnight batch job to export from the AS400 to an Access
or Sql database and let your users bang around in there. That will insure
that you do not have an unfortunate AS400 experience. Good luck with your
project.

 

[DavidAtCaspian]

I have found IBMs own ODBC driver to be perfectly reliable in both reading
and writing: Both via DAO and ADO. (In my experience!). A few tips though:
1) In order to run an update on the 400, you MUST have unique records.
2) Get someone who knows about these things to set up your DSN. IBM give
many settings (include read/write) as an option, most of which make no
difference.
3) You may have to set translate code page 65535 (or something like that),
or you will get fields full of "40" all over the place.
4) If working with large recordsets, or complex queries, it can be rather
slow. - You may need your AS400 guys to review the 'time out' settings.
5) On the PC side, be aware of the time out. There is an inbuilt 'safety' of
(I think) 60 seconds of AS400 processing, which the machine calculates first.
It can be overridden.
6) If you are running an update in code, and Access claims not to be
responding. Ignore it. It WILL still be running.
7) If using Query 400 to generate a file for your ODBC to work with, be
aware that if there are any conversion problems (like maybe you have JD
Edwards dates, and normally convert the date on the AS400). The AS400 will
not produce a file at all, if there are ANY problem fields. -- even one. -
Write a conversion routine in ACCESS and run it afterwards. - It will be
quicker.
8) Depending on what you're doing, it may be a lot quicker to first run a
make table query, and then your analysis one. Make Table from the AS400 is
much faster than generating a recordset.
9) Please feel free to email me directly for any more tips. Most of the
'public' are not interested in AS400!!