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 ?
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
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 1  Reply