Stored Procedure

How can I write a storded procedure to an AS400 directly from Access or VB.
I'm familiar with using stored procedures via ADO objects.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It might be easier to install client access so that you get an installation of ODBC for AS/400 if this is not already installed.  
Add a new connection in control panel/ODBC 32.

Link an AS/400 physical file via Access using the new ODBC link you created.

Use the new link as if its a table in Access.

If your AS/400 is not a fast server model you should use the MS Access Make Table to create a local copy of the data first, so as to prevent unnecessary overhead on the AS/400.

Create the Stored procedure on the AS/400 as follows

pace this in a source member and use
RUNSQLSTM CL command to execute this SQL statement.






   OPEN C1;


The next VB program consists of a form with two input boxes and a command button to call the procedure.
Text2 is the control that the user enters the number into.
Text1 is the control that the customer name is returned to.
Command1 is the button to pass the parameter and call the procedure - and handle the results.

VB Declarations:

Dim env1 As rdoEnvironment
Dim conn1 As rdoConnection
Dim strsql As String
Dim ps As rdoPreparedStatement
Dim rs As rdoResultset

'Form Load event code

rdoEngine.rdoDefaultCursorDriver = rdUseOdbc

Set env1 = rdoEngine.rdoCreateEnvironment(" ", " ", " ")

strsql = "DSN=xxxxx;UID=xxxxxx;PWD=xxxxxx;"

Set conn1 = env1.OpenConnection(" ", rdDriverNoPrompt, False, strsql)

conn1.QueryTimeout = 3600

The user enters a customer number into Text2, and the following code is run when the command button is clicked:

strsql = "call SPLIB.GETNAMESET(?)"

Set ps = conn1.CreatePreparedStatement("NAMETEST", strsql)

ps(0).Direction = rdParamInput

ps(0).Value = CInt(Text2.Text)

Set rs = ps.OpenResultset(rdOpenStatic)

If rs.RowCount = 0 Then

    Text1.Text = "Not Found"


    Text1.Text = rs!CNAME

End If

Text2.SelStart = 0

Text2.SelLength = Len(Text2.Text)


WaillandAuthor Commented:
Can i Create the Procedure programmatically from VB? If yes, please help
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.


I have provided you with the answer for creating a DB2/400 stored procedure and you have rejected it. The AS/400 is NOT a PC based operating system. Its database and operating system are proprietry
Asta CuTechnical consultant & graphic designCommented:
This question is still open today and may just have been overlooked.  If you have been helped here, you may choose the comment which served you as the accepted answer to then grade and close so that others seeking help on this may find it in the PAQ database.  Or, due to the age of this question, if you have difficulty directly converting a comment to the accepted answer, as the expert to post as answer that best served your needs which will then return to you for acceptance.

If more information is required, please post details here.

Thank you,
Asta CuTechnical consultant & graphic designCommented:
This question is still open today, perhaps it was overlooked or just lost in the volumes.  Please return to this question to update it with comments if more information is needed to get your solution.  If you've been helped by the participating expert(s), you may just convert their comment to the accepted answer and then grade and close.  If an answer has ever been proposed you may not have this option to accept the comment as answer, if that is the case, ask the specific expert you wish to award to post an answer.     This benefits others who then search our PAQ for just this solution, and rewards the experts who have provided information.  A win/win scenario.  Please DO NOT accept this comment as an answer,  it is merely a reminder.
If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts.  If you'd like to delete this question, use the same process as above, but explain why you think it should be deleted.  Here is the Community Support link:
You can always click on your profile to see all your open questions, in the event you also have other open items to be resolved.   If your number of Questions Asked is not equal to the number of Answers Graded, choose to VIEW question history, and you'll quickly be able to navigate to your open items to close them as well.
I've had excellent help from experts-exchange through the years and find the real key to getting what I need is to remain active in all my questions, responding with results to suggestions until my solution is found, and recommend that highly.
Thank you very much for your responsiveness, it is very much appreciated.  
":0)  Asta
Your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you
wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question
QID/link(s) that it regards.

Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer

Please click you Member Profile to view your question history and keep them all current with updates
as the collaboration effort continues, to track all your open and locked questions at this site.  If
you are an EE Pro user, use the Power Search option to find them.


------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item
remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please
click this link

Thank you everyone.

Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response
is awarded, since they are not in the comment date order, but rather in Member ID order.  
No response from Asker, nor experts, 200 points refunded and moved to PAQ at zero points to close.
Moondancer - EE Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.