An Access Q, but I think it will have a VBA solution

[The Idea Dude]

I am importing a text file into a table. Sometimes the fields (first line in text file) change. Is there a way that Access can automatically detect if there are new fields and then add them to the table if required?

Thanks

 

[Andrew Poulsom]

It seems no. According to Access Help, to append the data to an existing table the first row of your text file must contain matching field names.

 

[dk]

Hello,

This can be done but is a bit involved. You could open the first line of your text file and get a list of all field names. You could then compare this list to the field names which exist in your table. If the field doesn't exist, then add it.

What sort of data is stored in the new fields? Is it text, numeric, dates or what?

Have a look at this code. You need to set a reference to the Microsoft ADO Ext 2.n for DDL and Security before it will work.

I don't blame you if you say sod it and do it manually, but the option is there

This only updates your table to include the new field names. It doesn't actually import any data - that's another part of the question.

Sub AmendTable()
    Dim strSourceFilename As String
    Dim strTableName As String
    Dim vFieldNames As Variant
    Dim lngCounter As Long

    Dim adoxCatalog As ADOX.Catalog
    Dim adoxTable As ADOX.Table
    Dim adoxColumn As ADOX.Column

    Dim blnFieldExists As Boolean

    'Set this to the name of your source text file
    strSourceFilename = "C:tempgirls.txt"

    strTableName = "Staff"

    'Now get the first line of your text file.  Put the field names
    'into an array.
    Open strSourceFilename For Input As #1
    Line Input #1, vFieldNames
    vFieldNames = Split(vFieldNames, vbTab)    'This assumes your data is tab delimited
    'adjust it to suit your needs
    Close #1

    'Now compare the field names in the table to those names
    'that we found in the text file.  If the text file has field
    'names that aren't in the table, then add them.

    'In this case we'll use ADOX to add fields to your table

    Set adoxCatalog = New ADOX.Catalog

    adoxCatalog.ActiveConnection = CurrentProject.Connection
    Set adoxTable = adoxCatalog.Tables(strTableName)

    'Loop through each field name from our text file.  See if the
    'field name exists in the table
    For lngCounter = 0 To UBound(vFieldNames)

        blnFieldExists = False

        For Each adoxColumn In adoxTable.Columns
            If adoxColumn.Name = vFieldNames(lngCounter) Then blnFieldExists = True: Exit For
        Next adoxColumn

        If blnFieldExists = False Then      'Need to add field to table

            'This is where it gets tricky - knowing which data type
            'to specify.  You could scan each row in the text file
            'but this could get messy.  adVarWChar is the default (255 text)
            adoxTable.Columns.Append vFieldNames(lngCounter), adVarWChar

        End If

    Next lngCounter


End Sub

 

[The Idea Dude]

Thanks for the idea Dan,

I will give it a go because automation is what I am chasing. Typically - dare I use the word - it will be text headings.

I will repost this thread if I run into trouble

 

[The Idea Dude]

On 2002-10-17 07:43, dk wrote:
Hello,

This can be done but is a bit involved. You could open the first line of your text file and get a list of all field names. You could then compare this list to the field names which exist in your table. If the field doesn't exist, then add it.

What sort of data is stored in the new fields? Is it text, numeric, dates or what?

Have a look at this code. You need to set a reference to the Microsoft ADO Ext 2.n for DDL and Security before it will work.

I don't blame you if you say sod it and do it manually, but the option is there

This only updates your table to include the new field names. It doesn't actually import any data - that's another part of the question.

Sub AmendTable()
    Dim strSourceFilename As String
    Dim strTableName As String
    Dim vFieldNames As Variant
    Dim lngCounter As Long

    Dim adoxCatalog As ADOX.Catalog
    Dim adoxTable As ADOX.Table
    Dim adoxColumn As ADOX.Column

    Dim blnFieldExists As Boolean

    'Set this to the name of your source text file
    strSourceFilename = "C:tempgirls.txt"

    strTableName = "Staff"

    'Now get the first line of your text file.  Put the field names
    'into an array.
    Open strSourceFilename For Input As #1
    Line Input #1, vFieldNames
    vFieldNames = Split(vFieldNames, vbTab)    'This assumes your data is tab delimited
    'adjust it to suit your needs
    Close #1

    'Now compare the field names in the table to those names
    'that we found in the text file.  If the text file has field
    'names that aren't in the table, then add them.

    'In this case we'll use ADOX to add fields to your table

    Set adoxCatalog = New ADOX.Catalog

    adoxCatalog.ActiveConnection = CurrentProject.Connection
    Set adoxTable = adoxCatalog.Tables(strTableName)

    'Loop through each field name from our text file.  See if the
    'field name exists in the table
    For lngCounter = 0 To UBound(vFieldNames)

        blnFieldExists = False

        For Each adoxColumn In adoxTable.Columns
            If adoxColumn.Name = vFieldNames(lngCounter) Then blnFieldExists = True: Exit For
        Next adoxColumn

        If blnFieldExists = False Then      'Need to add field to table

            'This is where it gets tricky - knowing which data type
            'to specify.  You could scan each row in the text file
            'but this could get messy.  adVarWChar is the default (255 text)
            adoxTable.Columns.Append vFieldNames(lngCounter), adVarWChar

        End If

    Next lngCounter


End Sub

How do I adjust this to allow for comma delimited

vFieldNames = Split(vFieldNames, vbTab) 'This assumes your data is tab delimited
'adjust it to suit your needs


Thanks

 

[Andrew Poulsom]

Replace vbTab with ",".

 

[The Idea Dude]

Thanks,

now i don't know why vb is not liking adox (whatever that is - my help file won't let me select these entries)

Any advice

 

[Andrew Poulsom]

You need a reference to "Microsoft ADO Ext. 2.5 for DDL and Security" (Tools, References in the VBE).

 

[The Idea Dude]

OK, I have got it kind of working now.

It is havig trouble identifying the little square in the txt file the represents the end of a line, so I am getting an error about valid characters or being too long.

NB, I had to put """,""" for csv otherwise The field name would be in talking marks

Any more ideas,

Thanks

 

[Andrew Poulsom]

If "the little square" is the last element in the array returned by Split, then change:

For lngCounter = 0 To UBound(vFieldNames)

to

For lngCounter = 0 To UBound(vFieldNames) -1