Link to home
Start Free TrialLog in
Avatar of JArndt42
JArndt42Flag 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?
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

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
Avatar of 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
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry. I have not been on here for some time. Thanks for your help Gary