Create Index Statement Example

To create an index get into the create new query wizard and then close the Show Table dialog box without specifying a table or query. Change to SQL view and type the index creation DDL statement.

The following example creates a non-unique index on the field emp_phone in the Employees table.

CREATE INDEX Emp_Phone_IDX ON Employees (emp_phone);

Now let’s drop (delete) this index so we can show additional options for the Create Index statement:

DROP INDEX Emp_Phone_IDX on Employees;

Here’s an example to create a Unique index:

CREATE UNIQUE INDEX Emp_Phone_IDX ON Employees (emp_phone);

There are several other options to the create index statement – we have shown the common options.

One powerful feature of the create index statement is to create an index on an ODBC linked table.  This is usually referred to as a pseudo index and can significantly improve the performance of slow ODBC queries by adding an index on field references in the right side of where clauses in queries.

Here is the syntax for the create index statement from Microsoft Access 2003 help:

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 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.

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016