Retrieve list of indexes in an Access database

Question

I know there's a way to get a list of all tables in an Access database by using the quering the MsysObjects: "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4)<>'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name"

Does anybody know a similar (or other) way to retrieve a list of all indexes in an MS-Access database?

Answer 1

I don't think you can use a SELECT statement to list index names. You can examine TableDef objects to access the index names.

Public Sub ShowIndexNames()
    Dim tdf As TableDef
    Dim idx As Index
    Dim num_indexes As Long

On Error GoTo ErrorHandler

    For Each tdf In CurrentDb.TableDefs
        num_indexes = tdf.Indexes.Count
        If Left$(tdf.Name, 4) <> "MSys" Then
            If num_indexes > 0 Then
                For Each idx In tdf.Indexes
                    Debug.Print tdf.Name, idx.Name
                Next idx
            End If
         End If
    Next tdf

ExitHere:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3110
        'Could not read definitions; no read definitions '
        'permission for table or query '<Name>'. '
        Debug.Print "No read definitions permission for " _
            & tdf.Name
        num_indexes = 0
        Resume Next
    Case Else
        Debug.Print Err.Number & "-> " & Err.Description
        GoTo ExitHere
    End Select
End Sub

Edit: Revised the sub to ignore MSys* (Access system) tables.

Answer 2

Great! That works fine for me. I can even see if a index is unique or primary. One point: I've to delete the MSys stuff, but this is no problem.

One question: why do I see more indexes than there really are? I already did a Compact Database