columns in a table

[Jacob Mathai]

How can I programatically find the names of all columns in a table?

Thanks in advance.

 

[o1110010]

You can get a name (and other attributes) of a column with a property of Fields.

rst.Fields(x).Name
rst.Fields(x).FieldSize
rst.Fields(x).Size
rst.Fields(x).Type

x being any interger. 0 (zero) represents is the first field/column.

Here is one link that may give you more detail.

 

[theprez]

I use this:

Function listfields(strTableName As String) As String
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim Field As Field
Set db = CurrentDb
strSQL = "SELECT * FROM " & strTableName
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
For Each Field In rs.Fields
listfields = Field.Name 'You can send to an array or another table here
Next
End Function

Here is a start. You pass the table name to this function. Instead of passing the table name, you could have another For loop that goes through all your tables (skip system tables - there are posts on this site on how to do that.) and list all field names for all your pertinent tables.

Also you could send the name to an array or write to a new table, etc...
What do you need to do with the table names?

 

[Pat Hartman]

Here's some code that prints all the properties of the fields in a table including the field names. You should be able to adapt it to your purposes.

Sub EnumerateFieldX()

    Dim db As DAO.Database
    Dim tdfNew As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim prpLoop As DAO.Property
    
    On Error GoTo EnumerateFieldXError
    Set db = CurrentDb()

    Set tdfNew = db.TableDefs("tblTableFields")
    Debug.Print "Properties of new Fields in " & tdfNew.Name

    ' Enumerate Fields collection to show the properties of
    ' the Field objects.
    For Each fldLoop In tdfNew.Fields
        Debug.Print "  " & fldLoop.Name

        For Each prpLoop In fldLoop.Properties
            ' Properties that are invalid in the context of
            ' TableDefs will trigger an error if an attempt
            ' is made to read their values.
            On Error Resume Next
            Debug.Print "    " & prpLoop.Name & " - " & _
                IIf(prpLoop = "", "[empty]", prpLoop)
            On Error GoTo 0
        Next prpLoop

    Next fldLoop

    db.Close
EnumerateFieldXError:
    Select Case Err.Number
        Case 0 ' no error
        Case Else
            MsgBox Err.Number & " - " & Err.Description
    End Select
End Sub

When prpLoop.Name = "Name", prpLoop contains the field name.

 

[Jacob Mathai]

Thanks to all of you. You are all great in helping others.