‘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