#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    12
    Rep Power
    0

    Question Send command to AS400 via vba code


    Does anyone know how to send a simple command to the as400 via Vba so that I could run a query within an excel VBA program?
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,424
    Rep Power
    4539
    Please elaborate. Maybe you should be looking at the as400 side for information. VBA can manipulate serial, parallel, winsock, ado and other types of computer connections.
    ======
    Doug G
    ======
    The man who doesn't read good books has no advantage over the man who can't read them.
    --Mark Twain
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Well, connectionstrings tells me the connection for as400 is

    Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUserName;Pwd=myPwd
    so, if this is correct then ...

    vb Code:
    Sub SimpleCodeII()
        Dim cn0 As New ADODB.Connection 
        Dim rs0 As New ADODB.Recordset 
        Dim strConString As String
        strConString = "Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUserName;Pwd=myPwd"
        cn0.Open strConString
        rs0.Open "SELECT * FROM Something;", cn0, adOpenStatic, adLockReadOnly
    ' // do whatever with your recordset
        rs0.Close
        cn0.Close
    End Sub
    Last edited by medialint; July 11th, 2006 at 09:19 PM.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    12
    Rep Power
    0

    send command to AS400 via VBA code


    Sorry for not responding to your replies Doug G and Medialint, but I had a Bereavement in my family..
    Anyway.. I have already been able to send SQL commands much like you described Medialint, but I am trying to run normal CL commands first, before I use that particular coding. ie running a wrkqry to produce a database output before running the select or update (etc etc) commands. So I suppose I am really asking can you pass CL commands to the As400 via VBA?
  8. #5
  9. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,424
    Rep Power
    4539
    Can you pass CL commands to an AS400 via any external windows program? If so find out how they did it and you should be able to clone the mechanism in VB (maybe not VBA though).
    ======
    Doug G
    ======
    The man who doesn't read good books has no advantage over the man who can't read them.
    --Mark Twain
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    12
    Rep Power
    0
    No, I have no external Windows programs that access the As400 other than Client access, but I have no code for this and don't think it would be possible to see what is going on in it.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    1
    Rep Power
    0

    Send command to AS400 via vba code


    You should be able to send a command to your AS400 using this vba function:

    Public Function AS400RunCommand(ByVal strUserName As String, _
    ByVal strPassword As String, _
    ByVal strCmd As String) As Boolean
    ' set up error handling
    On Error GoTo ErrorHandler
    ' set the initial state of the function
    AS400RunCommand = False
    Dim AS400Conn As New ADODB.Connection
    Dim AS400Pgm As New ADODB.Command
    ' open a connection to the AS400
    AS400Conn.Open "Provider=IBMDA400;Data Source=PACIFIC;User ID=" & _
    strUserName & ";Password=" & strPassword & ";"
    Set AS400Pgm.ActiveConnection = AS400Conn
    ' create the command text
    AS400Pgm.CommandText = "CALL QSYS.QCMDEXC('" & strCmd & "', " & _
    Format(Len(strCmd), "0000000000") & ".00000)"
    ' execute the command
    AS400Pgm.Execute
    ' set the final state of the function
    AS400RunCommand = True
    ExitHere:
    Exit Function
    ErrorHandler:
    MsgBox "Error:" & Err.Number & vbNewLine & _
    "Description:" & Err.Description & vbNewLine & _
    "From:" & Err.Source, vbExclamation, _
    "AS400RunCommand Failed"
    Resume ExitHere
    End Function
  14. #8
  15. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    725
    cwood,

    Thank you for your contribution, but it's doubtful that the Op is still looking for an answer to this question, since it was posted 2.5 years ago.

    Thanks.

    Comments on this post

    • medialint agrees
    Fisherman

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

IMN logo majestic logo threadwatch logo seochat tools logo