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

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

Answer 1

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.

You could also use ADO's OpenSchema method to retrieve information about indexes. The code below lists the index name, associated table, and whether the index is the primary key. I wrote it to use late binding for ADO because that doesn't require setting the reference for Microsoft ActiveX Data Objects [version] Library.

Const adSchemaIndexes As Long = 12
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim i As Long

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaIndexes)
With rs
    ' enable next three lines to view all the recordset column names
'    For i = 0 To (.Fields.Count - 1)
'        Debug.Print .Fields(i).Name
'    Next i
    Do While Not .EOF
       Debug.Print !TABLE_NAME, !INDEX_NAME, !PRIMARY_KEY
       .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set cn = Nothing

If you prefer to examine indexes for a single table rather than for every table in the db, pass the table name as the fifth element of an array.

Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblFoo"))