Receiving data from and AS/400 ICF file into a VB Application

We're trying to receive data from an AS/400, and all the fields from the AS/400 database are being passed back through and ICF file as one long string. Rather than hard coding the positions of the variables, like "DepartmentText.Text = Mid$(sData, 7, 30)", what is the best way of extracting the individual variables from the incoming string? Surely there must be a more professional way of doing this, like calling a function that strips out the values, without you having to specify the position of each field?
We have considered passing the incomming string into the VB application, with the values separated by commas, and in VB, calling a generic function that scans through this string, and pulls out the varaibles.
The problem with this is we don't know how to call this generic application, because the number of variables could vary from application to application
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

"Surely there must be a more professional way of doing this, like calling a function that strips out the values, without you having to specify the position of each field?"

I wouldn't expect so. After all the AS/400 is not passing back any information about the file layout.

Alternatively though, would it be possible to use an ODBC connection to the AS/400 and access the files properly?
TGBAuthor Commented:
We're not using ODBC...... What we need is an example of how we can code this function, as described above.
Don't know, it looks like you're creating a lot of work for yourself, and to no avail. It doesn't matter whether you pass back comma separated files, you're still going to have to rely on your knowledge of what you're expecting to get back from the AS/400

So you may as well just hard-code the field lengths.

The only correct solution is to use a proper way of connecting to the AS/400 database. ODBC is one such way.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Are all the values separated by an identifier? (a comma, a semi-colon etc.) If so you would be able to split the string op into a class wrapper.
You would use the InStr function to isolate the fields in between the separator.
If not using a separator, then you would need to have format information somewhere. Perhaps in a wrapper class that  has inside the definition of the fields, or else an extra file generated on AS400 that holds formatting information.
TGBAuthor Commented:
Phiro: Cheers for a propper answer. Can you expand on the 'class wrapper'? Many thanks.

If I thought what I was saying was an "answer" I would have checked the "answer" box...

But I do feel very strongly about this, so don't dismiss my comments. I'm not sure what exactly you are trying to achieve here, but from reading your question I think I understand you want to make your file transfer a bit more reliable or something like that. That's why I commented like I did. Whereas phiro's suggestion might work, it is nothing more than a hack. Anything that does NOT involve the correct way of accessing database files, i.e. ODBC, ADO, RDO or any such methodology, is fundamentally flawed. And that's why I'm saying, "Why bother". No solution, no matter how clever is going to render you immune from problems caused by the fact that you don't know the exact file layout.

But hey, if you're adamant, who am I to tell you otherwise. But at least I tried.
TGBAuthor Commented:
I'm only of the understanding that using APPC programs and ICF files is the most efficient way of producing a client server application. Appologies for seeming bullish, but this is what we're stuck with, and we like it. So we're keen to solve this problem with the tools at our disposal.
Your comments are appreciated.....
TimCotteeHead of Software ServicesCommented:
I am going to support caraf_g on this one, we have an AS/400 here linking to VB and other apps using ODBC. It far outstrips the old way we used to do things using the AS/400 file transfer and then parsing the strings received as you describe. The Client Access ODBC driver works extremely well. If you are using the file transfer method, then there should be a .ttf/.tto file which identifies the structure of the file that is being transferred and you can read this to determine how to break up the strings. I would still recommend that you look at using ODBC as it will give you much greater flexibility and more immediate access to your data.

I'm working on this at the moment, positional files are a pain to load into VB.  Make a comma delimitted file on the AS/400 using the above command then import with

open "file" as #1 for input
while not(eof(1))
input #1,field1,field2,...etc

TGBAuthor Commented:
Cool! What are you using to transfer data from/to the 400? ODBC or other?
Sounds like a simple question, but we are currently using APPC....
I'm creating the file on the AS/400 hopefully to EMail it directly form the AS/400.  but also I've downloaded it via Client Access and then uploaded into VB and Access.  I'm not sure why the means of downloading the file would be an issue though for a text file.

How about starting your data off with data about the file you are downloading derived from the AS/400 via a command such as


You could process this data on the as/400 to give a header record telling VB what all the fields are, where they are and what data format they contain.  Then you could parse them into a two dimensional array of variants with the second dimension being varied according to the number of fields.

TGBAuthor Commented:
Cheers for the suggestion, but we are working with an interactive system, so bulk, one off file transfers are not suitable for our purpose.
With regards to your project, have you tried using Netsoft Transfer as you can create CSV files on the PC directly from your data base on the 400.
Bob LearnedCommented:
You could do something like this:

Private Type Test2
   a As String * 3
   b As String * 4
   c As String * 5
End Type

Private Type Test1
   a As String * 12
End Type

Private Sub BreakApartString()

Dim t1 As Test1
Dim t2 As Test2

   t1.a = "ABCdefgHIJKL"
   LSet t2 = t1
   Debug.Print t2.a, t2.b, t2.c
End Sub

You get t2.a = "ABC", t2.b = "defg", and t2.c = "HIJKL"

If your string is comma delimited you can use the split function. (you don't need to know how many fields you have.)

' Print the fields in Text1, labeling them with their field numbers.
Dim fields() As String, i As Integer
fields() = Split(Text1.Text, ",")
For i = 0 To UBound(fields)
    Print fields(i) & ": " & fields(i)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wow, that's an interesting discussion.
Well, here's what I have used in an previous project.

You have a form with two buttons and a flexgrid
Mind you, there is no error check at al in this demo, but it retrieves fields from a specific formatted string and creates objects from that...

Have fun!

***CODE FORM******
Option Explicit
Private sFileString As String
Private oRecords As New CRecords

Private Sub cmdLoadGrid_Click()
End Sub

Private Sub cmdReadString_Click()
    oRecords.ReadString sFileString
End Sub

Private Sub Form_Load()
    sFileString = "Robbie;201;21-1-1987,Jannie;202;12-3-1970,Joop;203;1-1-1947"
End Sub

Private Function FillGrid()
    'Vult het grid met gegevens:
    Dim oRecord As CRecord
    grdRecords.Cols = 3
    grdRecords.Rows = 1
    For Each oRecord In oRecords
        With grdRecords
            .Rows = .Rows + 1
            .Row = .Rows - 1
            .Col = 0
            .Text = oRecord.sName
            .Col = 1
            .Text = oRecord.lNumber
            .Col = 2
            .Text = oRecord.dDateOfBirth
        End With
    Next oRecord
End Function

Private Function Enabling()
    cmdLoadGrid.Enabled = Not (oRecords.Count = 0)
End Function

****END CODE FORM*****

And two classes :
Option Explicit

'local variable to hold collection
Private mCol As Collection

Public Sub ReadString(sFileString As String)
    'Assumed you have read the string into the parameter string:
    'Now load the parameters for all three fields you have read
    Dim bLastRecord As Boolean
    Dim lStartPos As Long
    Dim lEndPos As Long
    lStartPos = 1
    Dim lEndOfRecordSet As Long

    Dim vEndOfRecord As Variant
    vEndOfRecord = 0
    lEndOfRecordSet = Len(sFileString)
    bLastRecord = False
    Dim oRecord As CRecord
    Do While bLastRecord = False
        lStartPos = vEndOfRecord + 1
        vEndOfRecord = InStr(vEndOfRecord + 1, sFileString, ",", vbTextCompare)
        If IsNull(vEndOfRecord) = True Then
            Exit Do
        End If
        If vEndOfRecord = 0 Then 'If not found then the result is 0
            vEndOfRecord = lEndOfRecordSet
            bLastRecord = True
        End If
        Set oRecord = New CRecord
        With oRecord
            lEndPos = InStr(lStartPos, sFileString, ";", vbTextCompare)
            .sName = Mid(sFileString, lStartPos, lEndPos - lStartPos)
            lStartPos = lEndPos + 1
            lEndPos = InStr(lStartPos, sFileString, ";", vbTextCompare)
            .lNumber = CLng(Mid(sFileString, lStartPos, lEndPos - lStartPos))
            lStartPos = lEndPos + 1
            'Last field till the end of the record
            .dDateOfBirth = Mid(sFileString, lStartPos, vEndOfRecord - lStartPos)
        End With
        Add oRecord
End Sub

Public Function Add(oInput As CRecord) As CRecord

    If Len(oInput.Key) = 0 Then
        mCol.Add oInput
        mCol.Add oInput
    End If

    'return the object created
    Set Add = oInput
    Set oInput = Nothing

End Function

Public Property Get Item(vntIndexKey As Variant) As CRecord
    'used when referencing an element in the collection
    'vntIndexKey contains either the Index or Key to the collection,
    'this is why it is declared as a Variant
    'Syntax: Set foo = x.Item(xyz) or Set foo = x.Item(5)
  Set Item = mCol(vntIndexKey)
End Property

Public Property Get Count() As Long
    'used when retrieving the number of elements in the
    'collection. Syntax: Debug.Print x.Count
    Count = mCol.Count
End Property

Public Sub Remove(vntIndexKey As Variant)
    'used when removing an element from the collection
    'vntIndexKey contains either the Index or Key, which is why
    'it is declared as a Variant
    'Syntax: x.Remove(xyz)

    mCol.Remove vntIndexKey
End Sub

Public Property Get NewEnum() As IUnknown
    'this property allows you to enumerate
    'this collection with the For...Each syntax
    Set NewEnum = mCol.[_NewEnum]
End Property

Private Sub Class_Initialize()
    'creates the collection when this class is created
    Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
    'destroys collection when this class is terminated
    Set mCol = Nothing
End Sub

****END CODE****************

*******CRECORD CLASS*******
Option Explicit

Public Key As String

'local variable(s) to hold property value(s)
Private mvarsName As String 'local copy
Private mvarlNumber As Long 'local copy
Private mvardDateOfBirth As Date 'local copy
Public Property Let dDateOfBirth(ByVal vData As Date)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.dDateOfBirth = 5
    mvardDateOfBirth = vData
End Property

Public Property Get dDateOfBirth() As Date
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.dDateOfBirth
    dDateOfBirth = mvardDateOfBirth
End Property

Public Property Let lNumber(ByVal vData As Long)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.lNumber = 5
    mvarlNumber = vData
End Property

Public Property Get lNumber() As Long
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.lNumber
    lNumber = mvarlNumber
End Property

Public Property Let sName(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.sName = 5
    mvarsName = vData
End Property

Public Property Get sName() As String
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.sName
    sName = mvarsName
End Property


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.