determining names of idexed fields in a tabledef

[Ed S.]

Need some help finding the names of the indexed fields in a table def.

Following code gets me into the database definitions, but finding name(s) of indexed fields is eluding me.


Option Compare Database
Option Explicit

Dim db As Database
Dim tbl As tabledef
Dim fld As Field
Dim idx As Index
Dim strnm As String
Dim i As Integer, iTableCount As Integer
Dim tdfCurrent As tabledef
Dim strIndexNames as String

Sub test()
Set db = CurrentDb

iTableCount = db.TableDefs.Count

'For each Table,
For i = 0 To (iTableCount - 1)
Set tdfCurrent = db.TableDefs(i)

'Exclude system tables
If Left(tdfCurrent.Name, 4) <> "MSys" Then

??????
HERE IS WHERE I NEED HELP. HOW DO I READ THROUGH TABLE DEF'S INDEX COLLECTION AND RETRIEVE NAME. WANT TO PLACE IN:

strIndexNames = idx.fld.name

End If '

Next i

End Sub

 

[Ed S.]

Getting a little closer, but can't seem to find out how to strip the fieled's name out.

Here is revised version of code i am working with:

Option Compare Database
Option Explicit

Dim db As Database
Dim tbl As tabledef
Dim fld As Field
Dim idx As Index
Dim strnm As String
Dim i As Integer, iTableCount As Integer
Dim tdfCurrent As tabledef
Dim strIndexNames as String

Sub test()
Set db = CurrentDb

iTableCount = db.TableDefs.Count

'For each Table,
For i = 0 To (iTableCount - 1)
Set tdfCurrent = db.TableDefs(i)

'Exclude system tables
If Left(tdfCurrent.Name, 4) <> "MSys" Then
For Each idx In tdfCurrent.Indexes
xindexcount = tdfCurrent.Indexes.Count
For x = 0 To (xindexcount - 1)
NEED HELP HERE...
strIndexNames = idx.Fields
Next x
Next idx

End If '
Next i
End Sub


Problem is result of strIndexNames looks like: "+fld1;+fld2;+fld3"

How do I drop th + sign, and get only the field name?

 

[dmckinney]

Hi,

The following code will allow you to enumerate table and index properties including the fields. It's fairly generic type code, and once you understand the techniques you can apply it any collections pretty much.

Hope this helps.

dave.

p.s. to call type in immediate (debug) window (ctrl g) ?enumprops("mytable") where mytable is your table.

Option Compare Database
Option Explicit

Function enumprops(strTableName As String) as string
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim Idx As DAO.Index
Dim fld As DAO.Field
Dim strFieldNames As String

Set db = CurrentDb()

Set tdf = db.TableDefs(strTableName)
Dim prp As DAO.Property
For Each prp In tdf.Properties
    Debug.Print prp.Name & " " & prp.Value
Next

Debug.Print "**Indexes**"

For Each Idx In tdf.Indexes
    Debug.Print "Index - " & Idx.Name
    For Each prp In Idx.Properties
        Debug.Print prp.Name & " " & prp.Value
    Next prp
    Debug.Print "***Fields***"
    strFieldNames=""
    For Each fld In Idx.Fields
        Debug.Print fld.Name
        strFieldNames = strFieldNames & fld.Name & ","
        
    Next fld
    debug.print strFieldNames

    ' Strip out final comma
    If Len(strFieldNames) Then
        strFieldNames = Left(strFieldNames, Len(strFieldNames) - 1)
    End If

Next Idx


enumprops = "FIN"
End Function

This message was edited by dmckinney on 2003-01-09 06:08

 

[Ed S.]

Dave,

Really good stuff in this one. Really helps me (new to VBA programing) with understanding some fundamentals. And, the debug technique is so helpful.