RE: IBMDA400 and performance using VBA in Excel

["Grizzly M" <grizzlym@xxxxxxxxxxxxxxxxxxxxx>]

It might be because the cmd.execute is inside of a loop but I'm not sure
about that. IF you can expand the current cell range you might be able
to do the whole sheet with one cmd.execute, but I don't know the syntax
and I don't know if the IBMDA400 provider will support that. 
You might want to try a recordset.

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
   
Dim x As integer
   
' Set up ADO connection object properties. This connection will be read
only
' Depending on user's Client Access Settings they may or may not be
prompted
' to enter their user name and password to the AS/400 
With conn
   conn.Provider = "IBMDA400"  'IBM Data Access o the AS/400 provider
   conn.Properties("Data Source") = "192.168.1.1" 'Name of our AS/400
   conn.Open 'Open Connection.
   conn.CursorLocation = adUseServer
End With

rst.Open ("Select * FROM Lib.File"), conn, adOpenDynamic,
adLockOptimistic
   
For Each Row In Me.Rows
   x = x + 1
   If Me.Cells(x, 1) <> " " Then
      rst.AddNew
      rst.Fields(0) = Me.Cells(x, 1).Value
      rst.Fields(1) = Me.Cells(x, 2).Value
      rst.Update
   Else
      Exit For
   End If
Next

Rst.close
Conn.close

Rst = nothing
Conn = nothing

It's kind of hoaky, but it works.  The for loop should be changed not to
check each row in Me.Rows because that checks the maximum number of
allowable rows in a spreadsheet. Also the IF statement would need to be
altered in a fashion that would actually leave the for loop. This
example looks like it does, but it doesn't really work.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of nick
Sent: Thursday, January 05, 2006 7:54 AM
To: Midrange Forumn
Subject: 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.