Append upload data

[mo_in_france]

I'm using VBA combined with Client Access objects to upload Excel data
to the AS400.
I notice that everytime I do an upload, the data replaces the data
that exists on the AS400 file. I kind of assumed that there would be a
property in the "DatabaseUploadRequest" which would allow me to
specify append/ replace, but I'm unable to find it.

Does anyone have any suggestions on how I can append the data rather
than replace.

Please find below the code that I am using

' Declare variables
Dim as400 As New cwbx.AS400System
Dim ulr As New cwbx.DatabaseUploadRequest

' Variablesrequired for configuratin
Dim cmd As New cwbx.Command
Dim dt As New cwbx.DatabaseTransfer

With as400
' Specification of relevant server, so as to be able to use
the object AS400System
.Define "LASG" 'systemNames.DefaultSystem
End With

''''''' Definition of Upload'''''''''''''''
' Definition of Command property of Command object
Set cmd.system = as400

' Definition of System property of the Upload object
With ulr
Set .system = as400
''''''''''' Definition and extraction of PC options for data
to upload/ upload'''''''''
' Definition of name of physical file on server which is to be
updated
.AS400File.Name = "FOLDER/Table"
' Define the PC file to be uploaded
.pcFile.Name = "myPCFileName"
' Definition of the Descriptive FDF file (which identifies the
presentation of the file to be updated
.pcFile.fileDescriptionFile = "MYFDFFILE.fdf"
'Final upload
.Upload
End With

Any help or ideas would be well received.

Regards

Mohsen

[CRPence]

This is just a SWAG based on a similar example I have seen.... I have
no experience using the functions in VBA.
What happens if a "Data Transfer To iSeries" request is configured,
which defines the "Append to existing member" option, then instead of
using the dt.Upload, use that saved .dtt request with:
dt.Transfer "c:\mysavedrequests\mytransfer.dtt" ?

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

mo_in_france wrote:
> I'm using VBA combined with Client Access objects to upload Excel data
> to the AS400.

> I notice that every time I do an upload, the data replaces the data

[I_am...@hotmail.com]

You can write SQL insert statments for your file. Its quite simple
and works really well.

[mo_in_france]

> and works really well.- Hide quoted text -
>
> - Show quoted text -

Thanks for your assistance folks. I ended up using the transfer method
as suggested. The only downside to this was that I had to create a
transfer request file on the fly, because the PC file specified in it
would change every time. This is not a particularly big deal, but
ideally I would like to have avoided it. The good thing about this
method is that I can control a lot of the transfer options by creating
a transfer file.

With regards to using SQL insert statements... I've done this before
on another program, but I didn't like it. I had to insert one line at
a time. From the little I know of the OLE DB provider, it seems that
you cannot do multirow inserts. Updating one line at a time was
unacceptable, and hence I took my first step into programming the
Client Access objects.
My only problem now with this transfer is the problems caused by
specifying the UserID/ Password. When I specify them in the program
(so that the user does not have to enter them), the program fails due
to a unicode error. However, if I don't specify the userid/ password
in the program, and am forced to enter the id/ password I don't get
the error. I'm pretty sure it's got something to do with specifying
the CCSID translation, but I can' t get to the bottom of it.

Regards