IBMDA400 and performance using VBA in Excel

Greetings all,

 

I'm not sure if this is the correct place to post this, but please correct
me if its not.

 

I wrote a VBA proc to upload records from an Excel spreadsheet to the
iSeries. I realize I can use IBM's file upload addin for Excel, but chose
not to. When the code runs, it take a very long time. I looked at the job
running on the iSeries  and noticed it opening and closing the file I'm
uploading to for each row in the spreadsheet. A snipit of the code is
follows: 

 

Set cn_iSeries = CreateObject("ADODB.Connection")

cn_iSeries.Open "provider=IBMDA400;data source=S103LB4M;", "", ""

Set rs = CreateObject("ADODB.Recordset")

Set cmd = CreateObject("ADODB.Command")

Set cmd.ActiveConnection = cn_iSeries

  

Set currentcell = Worksheets(ActiveSheet.Name).Range("A3")

 

Do Until currentcell.Value = ""

  

  cmd.CommandText = "INSERT INTO library.file VALUES ("

  cmd.CommandText = cmd.CommandText & "'" & currentcell.Offset(0, 0).Value &
"'" & ","

  cmd.CommandText = cmd.CommandText & "'" & currentcell.Offset(0, 1).Value &
"'"

  cmd.CommandText = cmd.CommandText & ")"

  

  cmd.CommandType = adCmdText

  cmd.Execute

  

   Loop  

 

 

I was wondering if there is a better way performance wise to do this. It
would be nice if the iSeries wouldn't open and close the file for each row,
but I'm not sure if that can be controlled or not.