Listing Field Properties with VBA

[GohDiamond]

Hello again,

Is there a way to gain access to the Field Properties using VBA?
I'm trying to loop thru the Tables in a database and find out the Fields that are indexed in each table.

I can loop through the table Defs to get each table name and a list of Fields but I can't seems to get to the Field Properties, namely "INDEXED".

Thanks in advance for your assistance.
Goh

 

[gemma-the-husky]

you may need to set variables for
currentdb, the active tabledef, and the active field to get this to work

 dim prp as property
 for each prp in currentdb.tabledef("tablename").fields("fieldname")
    msgbox prp.name
 next

note
msgbox prp.name & " Value: " & prp.value

may fail, as not every property has a value. you can use error handling, though, to avoid crashing the programme.


the indexed property may not actually exist, or may not actually be called "indexed". you may instead need to iterate the tabledef.indexes collection. not sure, offhand

I can't put my hand on it, but I have an excellent DAO bible from O'Reilly. Highly recommended, if you need that level of detail

 

[GohDiamond]

Thanks for the Quick repsponse. While I was waiting I kept researching and found a previouos post by you here: http://www.access-programmers.co.uk/forums/showthread.php?t=260032&highlight=field+properties

I have adapted your code in the post into the TableInfo() function posted on Allen Browne's site and got it to work.

However, "INDEXED" is not one of the Field options under fld.properties.
These are the results I got on the field "Company" from the "Customers" table of the Northwind dB, which is indexed:

Attributes 2
CollatingOrder 1033
Type 10
Name Company
OrdinalPosition 1
Size 50
SourceField Company
SourceTable Customers
DataUpdatable False
DefaultValue
ValidationRule
ValidationText
Required False
AllowZeroLength False
AppendOnly False
ColumnHidden False
Description Company Name
IMEMode 0
IMESentenceMode 0
UnicodeCompression True
AggregateType -1
ColumnWidth -1
ColumnOrder 0
DisplayControl 109
TextAlign 0
WSSFieldID Company
GUID ????????​

So, I'll try your suggestion above and let you know what happens. Btw, what is the name of that reference book? I may need it

Goh

 

[jdraw]

I have this from years ago, you can adapt as you wish/need if it is applicble.

Sub IndexesAllTables()
          Dim db As DAO.Database
          Dim tdf As DAO.TableDef
          Dim idx As index

10        On Error GoTo IndexesAllTables_Error

20        Set db = CurrentDb
30        For Each tdf In db.TableDefs
40            Debug.Print tdf.name; "   " & tdf.Indexes.Count
50            For Each idx In tdf.Indexes
60                Debug.Print "  ---- " & idx.name & "  " & IIf(idx.Primary, "Primary", "Not PK") & "  " & IIf(idx.Unique, "Unique", " ")
70            Next idx
80        Next tdf

90        On Error GoTo 0
100       Exit Sub

IndexesAllTables_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure IndexesAllTables of Module AWF_Related"

End Sub

 

[gemma-the-husky]

I just put my hands on the book

DAO Object Model (Helen Feddema - O'Reilly)