Getting Table Field Names

  • Thread starter Thread starter Chaplain Doug
  • Start date Start date
C

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.
 
Chaplain said:
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?
Click to expand...


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
 
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
 
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?
 
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
Click to expand...
..
 
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
 
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
 
Back
Top