Getting Table Field Names

[Chaplain Doug]

How may I programmatically get the field names from a
table? Is there a way to get the number of fields total
for a table and then loop through grabbing the field
names? Thanks for the help.

 

[Marshall Barton]

How may I programmatically get the field names from a
table? Is there a way to get the number of fields total
for a table and then loop through grabbing the field
names?

Dim db As Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb()
Set tdf = db.TableDefs("tablename")

For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

 

[chris]

try..

Dim tdf As TableDef
Dim fld As Field

For Each tdf In CurrentDb.TableDefs
For Each fld In tdf.Fields
Debug.Print tdf.Name, fld.Name
Next fld
Next tdf

 

[Guest]

I also want to get either the "Caption" specified for each
field or the "Description" shown in table design mode.
Neither of these appears to be available as a .Caption
or .Description like .Name is. Any suggestions?

 

[Chaplain Doug]

I also want to get either the "Caption" specified for each
field or the "Description" shown in table design mode.
Neither of these appears to be available as a .Caption
or .Description like .Name is. Any suggestions?

-----Original Message-----



Dim db As Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb()
Set tdf = db.TableDefs("tablename")

For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

..

 

[chris]

Use ...

Dim tdf As TableDef
Dim fld As Field

For Each tdf In CurrentDb.TableDefs
For Each fld In tdf.Fields
Debug.Print tdf.Name, fld.Name, fld.Properties
("Description").Value, fld.Properties("Caption").Value
Next fld
Next tdf

You will need to put error trapping around
the "description" and "caption" bits because if these
values have not been set, the respective properties will
not exist

 

[Chaplain Doug]

Thanks Chris!
..Fields(I).Properties("Caption").Value

 

[David]

For Each tbl In db.TableDefs
rTbl.AddNew
rTbl!TableName = tbl.Name
rTbl!Linked = (tbl.Connect > "")
iTableID = rTbl!TableID
rTbl.Update
For i = 0 To tbl.Fields.Count - 1
rClm.AddNew
rClm!TableID = iTableID
rClm!FieldName = tbl.Fields(i).Name
rClm!FieldType = tbl.Fields(i).Type
On Error Resume Next
rClm!Description =
tbl.Fields(i).Properties("description")

' rClm!FieldDesc = tbl.Fields(i).Attributes
rClm.Update
Next i
NextTable:
Next tbl