Need a list of variables in Tables

[Guest]

Hello,

I have an .mdb file with about 30 tables and each table has a dozen or more
fields. One table has 254 fields. I need to create a new table that
contains two columns: one column will show table names, and another will show
field names within that table. The new table will be my table/field
cross-reference table.

I tried using the utilities--documenter to create the list of items to go
into the new table, but can't seem to get the right format of data from
there.

What VBA code would do the job?

Thank you,

Keith

 

[Wayne Morgan]

A table with 254 fields make me suspicious as to whether or not the data is
properly normalized. However, to get the names of all tables in the database
and their fields, this can be done in VBA.

Example:
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCrossReference", dbOpenDynaset)
With rst
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "msys" Then
For Each fld In tdf.Fields
.AddNew
!TableName = tdf.Name
!FieldName = fld.Name
.Update
Next
End If
Next
End With
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Guest]

Hi Wayne,
thanks very much. that will help.
keith

 

[Duane Hookom]

If you don't like to code, you can use the documenter with instructions from
http://www.access.hookom.net/Documenter/.