Avatar of JArndt42
JArndt42
Flag for United States of America asked on

AS400 Stored Procedures

I have an access data base that I use successfully used pass through queries to get data from AS400 database. I want to know if there is a way to create a stored procedure on AS400 and call it from Access. Basically what I am trying to do is this. I have a carrier data base. the carriers are in the AS400 database and I view them in my Access database, allot more flexible and easier to find. Each carrier has a city. I want to run a query in Access that would find the carriers in so many mile radius of a search city. On the AS400 there is a PC miler version for AS400. I want to be able to pass the city and radius to that and get the results. Is this even possible? I would think I need to create a stored procedure and somehow pass the parameters to this from Access. Am I even close?
IBM System iMicrosoft Access

Avatar of undefined
Last Comment
JArndt42

8/22/2022 - Mon
Gary Patterson, CISSP

Yes, this is a pretty common solution to your kind of problem.  You'll just need to understand the PC Miler API on the AS/400 that you need to call, and then create a DB2 External Stored Procedure to call that program.

Here is a good IBM Redbook that explains how to create DB2 Stored Procedures:

http://publib-b.boulder.ibm.com/Redbooks.nsf/9445fa5b416f6e32852569ae006bb65f/559d4ee24ce894f685256ac40061faa3?OpenDocument

If you want to post the parameter list for the PC Miler program you want to call, we can help you create a stored procedure definition for it.

I don't use Access for these chores, but I do use Excel and VBScript.  The process is pretty easy:

IBM provides several different data providers for the AS/400.  Most are part of IBM i Access for Windows ("Client Access") - no relation to Microsoft Access - the names are just confusingly similar.  You may have to install them if they aren't already installed on your system.  There is an ODBC provider, an ADO provider and a .NET provider (plus a JDBC driver in JTOpen or the IBM i Toolbox for Java).

For VBA from Access, I suggest the ADO provider.  Here is a link to an article with sample VBA code:

http://www.mcpressonline.com/programming/apis/more-as400-clientserver-programming-with-ado-and-vba.html

- Gary Patterson
JArndt42

ASKER
Thank you Gary. I do have Client Access installed and I have installed the ODBC. That is ODBC I am using for my pass through queries I use in MS Access to get the information from the database we have on our AS/400 i. The first link has been removed from red book. the second contains some stuff I will be looking into. Not the answer but getting other ideas on how to work with IBM. As far as the PC miler API I have no idea whatsoever. I will leave this question open for a little bit to see if I can get detailed ideas on how to work with the API.
ASKER CERTIFIED SOLUTION
Gary Patterson, CISSP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JArndt42

ASKER
Sorry. I have not been on here for some time. Thanks for your help Gary
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy