Deleting an Index

Use the DROP CONSTRAINT clause to delete an index. You must specify the index name. The following procedure deletes a primary key index from the tblSchools table.

©Hands-On 19-13: Deleting an Index

This hands-on uses the tblSchools table created in Hands-On 19-2. You must perform Hands-On 19-11 prior to running this procedure.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the DeleteIndex procedure as shown below.

Programming with the Jet Data Definition Language

Sub DeleteIndex()

Dim conn As ADODB.Connection Dim strTable As String Dim strIdx As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strIdx = "pKey"

conn.Execute "ALTER TABLE " & strTable & _ " DROP CONSTRAINT " & strIdx & ";"

ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

After running the procedures in Hands-On 19-12 and 19-13, the Indexes window (see Figure 19-4 earlier) should be empty.

0 0

Post a comment