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 Remarks in this section.
Syntax
CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]
The CREATE INDEX statement has these parts:
Part | Description |
index | Name of the index to be created. |
table | Name of the existing table that will contain the index. |
field | 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:
You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as Microsoft SQL Server, that does not already have an index. You don’t need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. This can be especially useful to create an index on a table that would ordinarily be read-only due to lack of an index.
You can also use the ALTER TABLE statement to add a single- or multiple-field index to a table and you can use the ALTER TABLE statement or the DROP statement to remove an index created with ALTER TABLE or CREATE INDEX.
Note Don’t use the PRIMARY reserved word when you create a new index on a table that already has a primary key; if you do, an error occurs.