Hello,

I've searched the site and haven't seen this question posted and I'm hoping someone has a tip for me. I'm trying to create a form that will show for a specific table in my db the table characteristics. So for each field, I'd like to show field name, field size, data type, and whether the field is indexed with no duplicates. I can't seem to figure out how to write code to determine if a field is indexed no duplicates or unique. I've tried the following:

For Each idx In dbCurr.TableDefs(datasource).Indexes
Debug.Print idx.Name
Next

but instead of returing the name of the field that is indexed, it returns the value "PrimaryKey", which the field is the primary key but I'd like to get a field name or simply be able to loop through the fields and determine if they are unique. Does anyone have any ideas?

Thanks,
Rosebud

idx.Unique will return True or False.

You can loop through the Fields collection of each Index to get the field names.


Dim db As DAO.Database
Dim idx As Index
Dim fld As Field
Dim sFld As String

Set db = CurrentDb

For Each idx In db.TableDefs("Table1").Indexes

MsgBox "index name " & idx.Name
MsgBox "unique " & idx.Unique

sFld = "fields "
For Each fld In idx.Fields
sFld = sFld & fld.Name & ", "
Next fld
MsgBox Left(sFld, Len(sFld) - 2)

Next idx

Thank you so much EMP, your code worked great!