Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Retrieve list of indexes in an Access database

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?

like image 286
waanders Avatar asked Mar 29 '10 09:03

waanders


People also ask

How can we get the list of all the indexes on a table?

To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.

How do I view indexes in Access?

To open the Indexes window, on the Design tab, in the Show/Hide group, click Indexes. If you add a single-field index in the Indexes window, Microsoft Access will set the Indexed property for the field to Yes.

Where can you find the list of all Access database objects?

The Navigation Pane is the main way you view and access all your database objects and it displays on the left side of the Access window by default.


1 Answers

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"))
like image 142
HansUp Avatar answered Sep 27 '22 20:09

HansUp