Full Version: How to get a list of all a table's fields in a query
UtterAccess Forums > Microsoft® Access > Access Forms
Berty
Hi Guys
I have a table with 90 fields
What I want is to create a query, one field being named KPI listing all the 90 fields (so there would be in effect, 90 rows)
Any ideas?
Many thanks
jwhite
Greets,
For this you would need to enumerate the table list and pull out the field names. The code below will get the table and field names, and save them to two tables. You will need to first create (2) tables:
1. Table "tblTables", Field 1 "TableName" (Text 100)
2. Table "tblFieldsInTables", Field 1 "tableName" (Text 100), Field 2 "FieldName" (Text 100)
Then: SELECT FieldName FROM tblFieldsInTables WHERE TableName = "KPI"
(I'm assuming the table is KPI)
Hope that helps!
CODE
'LIST FIELDS IN ALL TABLES TO A SPECIFIC TABLE
Public Function TablesEnum() As Boolean
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Dim strSQL As String
    
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            strSQL = "INSERT INTO tblTables(TableName) VALUES('" & tdf.Name & "')"
            dbs.Execute strSQL, dbFailOnError
            For Each fld In tdf.Fields
                strSQL = "INSERT INTO tblFieldsInTables(TableName, FieldName) VALUES('" & tdf.Name & "','" & fld.Name & "')"
                dbs.Execute strSQL, dbFailOnError
            Next fld
        End If
    Next
    
    Set dbs = Nothing
    
End Function
Berty
Hi fdcusa
any thanks for your response here
I'll put it into action tonight
Many thanks again
All the bst
John
jwhite
Your welcome! Glad to help! sad.gif
If you have any issues, let me know here.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.