Asking help on adding index on Access 1

[edeaux]

Is it possible to create index in Access? If so how to do it?

 

[DomThePom]

Yes you can easily create indexes in Access:

In the user interface:

With the table in design mode, select the field that you want to index and then choose, as required, either "Yes (duplicates OK)" or "Yes (No duplicates)" in the Indexes section of the General properties tab. The index is actually created when you click on the save button.

To add more than one field to an index use the Indexes dilog (View / Indexes)

To create a Primary key, select the field or fields that you want in the primary key and then click on the primary key symbol in the toolbar.

In VBA the process is:
1. Create a new index in the table using CreateIndex
2. Append field(s) to the new index using createfield
3. Set the unique property of the index
4. Append the new index to the tabledefs index collection

Code to create a non-unique index on the CustomerType field in the Customers table (residing in the current database) would be as follows:


Dim idx As Index 'index to be created
Dim db As Database
Dim tdf As TableDef

set db = currentdb
Set tdf = db.TableDefs("Customers&quot
Set idx = tdf.CreateIndex("CustomerTypeIndex&quot
idx.Fields.Append idx.CreateField("CustomerType&quot
idx.Unique = False
tdf.Indexes.Append idx

set db = nothing
set tdf = nothing
set idx = nothing

 

[edeaux]

Thanks, DomThePom.