MS Access – AS400 file transfer

I am trying to move data from a .dbf file to the AS400. I have tried several differnet methods from access and none of them worked. I tried setting up an ODBC table pointing to the as400 and doing an update query and also an export. I could not get either of these to work. One of them kept bringing up a blank odbc definition instead of the ine I created. Has anyone done this ? Is it faster than AS400 client access file transfer ?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You failed to state which method you want
to use to accomplish this, Maually
Get external data. Via a Macro or
via visual basic.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Code2live
    You can use the IBM providers supplied in Iseries Client Access to access the AS400 directly using SQL statements. Here is an example:
    ‘Note: Provider IBMDA400 is installed on the local PC with IBM Iseries Client Access (Must be same version as your AS400 Operating system) 
    Public host As New ADODB.Connection ‘Connection to transfer data to AS400
    ‘Users Credentials
    Dim strAs400 as string ‘ String to hold the AS400 Insert Query
    host.Open "Provider=IBMDA400;Data Source=" & CnAs400 & ";user id=<valid As400Userprofile>;password=<valid As400UserPassword>"
    host.CursorLocation = adUseServer   ‘Runs the sql stmts on the server
    ‘Create the SQL statement to run, in this case it is an insert, so the values must be supplied. The assumption here is the variables to be inserted have been retrieved from another “local” table in the Access application.  Of course, in order to be “good” sql, the number and format of data values supplied in the VALUES clause must be the same as number and formats  of columns listed in the INTO clause..
    strAs400 = "INSERT INTO myAS400Table (<column 1 name>,<column 2 name>,<column 3 name>)" & _
                      "VALUES  (" & strColumn1Value & ",'" & strColumn2Value & _
                      strColumn3Value  & "')"
    host.Execute strAs400 ‘ performs the SQL statement in the strAs400 string
    ‘ TO start at batch job on the AS400, use the following
    DIM  strPrg as string 
    strPrg = "{{SBMJOB CMD(CALL PGM(mylib/myCLpgm) PARM('" & strFirstParm & "')) JOB(MyJobName) JOBQ(MyJobQ)}}"
    host.Execute strPrg ‘ starts the job as a batch job
    Hope this helps!
    55 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: