Listing Indexes in a Table

The Indexes collection contains all Index objects of a table. You can retrieve all the index names from the Indexes collection. The procedure below demonstrates how to list the names of indexes available in the Northwind database's Employees table in the Immediate window.

©Hands-On 12-4: Listing Indexes in a Table

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the List_Indexes procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub List_Indexes()

Dim conn As New ADODB.Connection Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim idx As New ADOX.Index

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb" End With cat.ActiveConnection = conn Set tbl = cat.Tables("Employees")

Creating and Manipulating Databases with ADO

For Each idx In tbl.Indexes

Debug.Print idx.Name Next idx conn.Close

Set conn = Nothing

MsgBox "Indexes are listed in the Immediate window." End Sub

0 0

Post a comment