Create Table of Field Names

[Bill Sturdevant]

How can I easily create a table containing the table names
and field names of all the other tables in a database?

 

[Allen Browne]

This routine assumes the target table named "TableInfo" has Text fields
called "TableName" and "FieldName":

Sub ShowAllTables()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTable As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableInfo", dbOpenDynaset, dbAppendOnly)

For Each tdf In db.TableDefs
'Skip system and hidden tables.
If ((tdf.Attributes And dbSystemObject) = 0) And ((tdf.Attributes
And dbHiddenObject) = 0) Then
'Skip attached and temporary tables
strTable = tdf.Name
If (Len(tdf.Connect) = 0) And Not (strTable Like "~*") Then
For Each fld In tdf.Fields
rs.AddNew
rs!TableName = strTable
rs!FieldName = fld.Name
rs.Update
Next
End If
End If
Next

rs.Close
Set rs = Nothing
Set db = Nothing

Debug.Print "Info written to table 'TableInfo'."
End Sub