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

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.

Code:
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

Well-known Member
Joined
Aug 15, 2002
Messages
585
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

Well-known Member
Joined
Aug 15, 2002
Messages
585

ADVERTISEMENT

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.

Code:
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
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need a reference to "Microsoft ADO Ext. 2.5 for DDL and Security" (Tools, References in the VBE).
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top