Deleting Table Indexes

Although you can delete unwanted or obsolete indexes from the Indexes window in the Microsoft Office Access 2003 user interface, it is much faster to remove them programmatically.

The procedure in Hands-On 12-5 illustrates how to delete all but the primary key index from the Employees table located in the Northwind database.

©Hands-On 12-5: Deleting Indexes from a Table

The procedure in this hands-on deletes all but the primary key index from the Employees table in the Northwind database. It is recommended that you prepare a backup copy of the original Northwind database prior to running this code.

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

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

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

Sub Delete_Indexes()

' This procedure deletes all but the primary key index ' from the Employees table in the Northwind database. ' Prior to running this procedure make a backup copy of ' the original Northwind database.

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

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb" End With cat.ActiveConnection = conn Setup:

Set tbl = cat.Tables("Employees")

Part II

Debug.Print tbl.Indexes.count For Each idx In tbl.Indexes

If idx.PrimaryKey <> True Then tbl.Indexes.Delete (idx.Name) GoTo Setup End If Next idx conn.Close

Set conn = Nothing

MsgBox "All Indexes but Primary Key were deleted." End Sub

Notice that each time you delete an index from the table's Indexes collection you must set the reference to the table because current settings are lost when an index is deleted. Hence, the GoTo Setup statement sends Visual Basic to the Setup label to get the new reference to the Table object.

0 0

Post a comment