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?
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.
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.
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.
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"))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With