DROP Statement

Description

Deletes an existing table from a database or deletes an existing index from a table.

Note The Microsoft Jet database engine doesn't support the use of DROP, or any of the DDL statements, with non-Microsoft Jet databases. Use the DAO Delete method instead.

Syntax

DROP {TABLE table | INDEX index ON table}

The DROP statement has these parts:

Part	Description

table	The name of the table to be deleted or the table from which an index is to be deleted.
index	The name of the index to be deleted from table.

Remarks

You must close the table before you can delete it or remove an index from it.

You can also use ALTER TABLE to delete an index from a table.

You can use CREATE TABLE to create a table and CREATE INDEX or ALTER TABLE to create an index. To modify a table, use ALTER TABLE.

See Also

ALTER TABLE statement, CONSTRAINT clause, CREATE INDEX statement, CREATE TABLE statement, Delete method ("DAO Language Reference").

Example

The following example assumes the existence of a hypothetical NewIndex index on the Employees table in the Northwind database.

This example deletes the index MyIndex from the Employees table.

Sub DropX1()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Delete NewIndex from the Employees table.
    dbs.Execute "DROP INDEX NewIndex ON Employees;"

    dbs.Close

End Sub

This example deletes the Employees table from the database.

Sub DropX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Delete the Employees table.
    dbs.Execute "DROP TABLE Employees;"

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following example, create an index called MyIndex on the Employees table in the Northwind database, by using any field. Then create a new table called Trainees. Create a new query in the Northwind sample database and close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Warning These examples makes changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

The following example deletes the index MyIndex from the Employees table:

DROP INDEX MyIndex ON Employees;

The next example deletes the Trainees table from the database:

DROP TABLE Trainees;