Description
Creates a new index on an existing table.
Note For non-Microsoft Jet databases, the Microsoft Jet database engine doesn't support the use of CREATE INDEX (except to create a pseudo index on an ODBC linked table) or any of the data definition language (DDL) statements. Use the DAO Create methods instead. For more information, see the Remarks section. Syntax CREATE [ UNIQUE ] INDEX indexPart | Description |
index | The name of the index to be created. |
table | The name of the existing table that will contain the index. |
field | The name of the field or fields to be indexed. To create a single-field index, list the field name in parentheses following the table name. To create a multiple-field index, list the name of each field to be included in the index. To create descending indexes, use the DESC reserved word; otherwise, indexes are assumed to be ascending. |
Remarks To prohibit duplicate values in the indexed field or fields of different records, use the UNIQUE reserved word.
In the optional WITH clause, you can enforce data validation rules. You can:See Also ALTER TABLE statement, CONSTRAINT clause, CREATE TABLE statement, CreateIndex method ("DAO Language Reference"), DROP statement.
Example This example creates an index consisting of the fields Home Phone and Extension in the Employees table.Sub CreateIndexX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create the NewIndex index on the Employees table.
dbs.Execute "CREATE INDEX NewIndex ON Employees " _
& "(HomePhone, Extension);"
dbs.Close
End Sub
This example creates an index on the Customers table using the CustomerID field. No two records can have the same data in the CustomerID field, and no Null values are allowed.
Sub CreateIndexX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a unique index, CustID, on the
' CustomerID field.
dbs.Execute "CREATE UNIQUE INDEX CustID " _
& "ON Customers (CustomerID) " _
& "WITH DISALLOW NULL;"
dbs.Close
End Sub
Example (Microsoft Access)
To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. 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.
CREATE INDEX NewIndex ON Employees (HomePhone, Extension);
The following example creates an index on the Customers table with the CustomerID field. No two records can have the same data in the CustomerID field, and no Null values are allowed.
CREATE UNIQUE INDEX CustID ON Customers (CustomerID) WITH DISALLOW NULL;
To delete the index created in the preceding example, you must use the DROP statement. You can't delete the index from table Design view unless you also delete the relationship on the Customers table. The following statement deletes the new index by using the DROP statement:
DROP INDEX CustID ON Customers;
The following example creates an index on an ODBC linked table. The table's remote database is unaware of and unaffected by the new index. The following example won't work if you simply paste it into Microsoft Access; you first need to create an ODBC linked table called OrderDetailsODBC.
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);