Autocreate Table of Field Names

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

Bill Sturdevant

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

The table I want to fill has essentially 2 fields in it:
TableName
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
 
Back
Top