Loop through Column names in an access table

[neemi]

Is it possible to loop through column names in an access table?

I need to do this prior to activating some code as a table is created by importing a spreadsheet to the db and I need to check that certain columns exist in this imported table in order for the rest of the procedure to work without error.

Cheers in advance.
Neemi

 

[TonyJollans]

Hi Neemi,

Like this ..

[blue]Dim db As DAO.Database
Dim fld As DAO.Field
Set db = CurrentDb
For Each fld In db.TableDefs![i]YourTable[/i].Fields
    MsgBox fld.Name [green]' Do what you want instead of this[/green]
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DeanWilliams]

Hi Neemi,

If you just need to check the column names and not see the data then you can use a TableDef, otherwise to access the data use a recordset. If you don't already have a reference set to the DAO library, go to Tools|References... in the VB Editor and select Microsoft DAO 3.x Object Library.

Here is tabledef code to check for the existence of a column:

Function FindColumn(strTableName As String, strColumnName As String) As Boolean

    Dim dbs As Database
    Dim fld As DAO.Field

    Set dbs = CurrentDb

    On Error Resume Next
    Set fld = dbs.TableDefs(strTableName).Fields(strColumnName)

    If Err = 0 Then FindColumn = True

    Set fld = Nothing
    Set dbs = Nothing

End Function

This will just return True or False depending on whether a field/column exists.

Good luck,

Dean