Deleting an Indexed Column

Deleting an index field is a two-step process:

1. Use the DROP CONSTRAINT clause to delete an index. You must specify the index name.

2. Use the DROP COLUMN clause to delete the desired column. You must specify the column name.

Both clauses must be used in the ALTER TABLE statement. The following procedure deletes the District column from the tblSchools table. Recall that the procedure in Hands-On 19-11 added a multiple-field index based on the SchoolId and District columns.

Part III

©Hands-On 19-12: Deleting a Field that is a Part of 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 DeleteIdxField procedure as shown below.

Sub DeleteIdxField()

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

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strCol = "District" strIdx = "multiIdx"

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

conn.Execute "ALTER TABLE " & strTable & _ " DROP COLUMN " & strCol & ";"

ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

0 0

Post a comment