How To Create Index In Access

To know about what the index is and its advantage and disadvantage and why we use index in access or any other database. Just follow the link…

'http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010210347.aspx


Here Is How To Create Index Programmatically.

To create index in access we need four objects and a global method or function
Objects

1.    Database
2.    TableDef
3.    Index
4.    Field
Method

1.    OpenDatabase
Syntax

Function OpenDatabase(Name As String, [Options], [ReadOnly], [Connect]) As Database
Function CreateIndex([Name]) As Index
Function CreateField([Name], [Type], [Size]) As Field

First we need to open a database using OpenDatabase which return a database as Database object as you can see in syntax section.We set the returned database object to our Database object variable like this.

Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\NWIND.mdb")

Then we need to set our TableDef object variable to the table to which we want to create index like this.

Set tdfEmployees = dbsNorthwind ! Employees

Symbol ! is used to get database table as TableDef Object. You can use the same line for other tables like this.

Set tdfEmployees = dbsNorthwind ! Products
Set tdfEmployees = dbsNorthwind ! Categories

And so on…

Next we need to use our tdfEmployees variable and its CreateIndex method to create new index “CountryIndex” and set it to our idxCountry variable like this.

Set idxCountry = tdfEmployees.CreateIndex ("CountryIndex")

Now we have named our new index to “CountryIndex” and next we need a new field to index.
To do so we need to use our tdfEmployees variable ‘s CreateField method to create new field and set it to our Nfield variable like this.

Set Nfield = tdfEmployees.CreateField("Country")

Next we need to append our newly created field to Fields collection and index to tdfEmployees object variable ‘s Indexes collection and tdfEmployees variable ‘s Refresh method to reflect changes to our UI(User interface) and finally close our database using dbsNorthwind object variable’s Close method to free up computer resources.

**Complete Code**

   Dim dbsNorthwind As Database
   Dim tdfEmployees As TableDef
   Dim idxCountry As Index
   Dim Nfield As Field

Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Visual     Studio\VB98\NWIND.mdb")

   Set tdfEmployees = dbsNorthwind!Employees
   Set idxCountry = tdfEmployees.CreateIndex("CountryIndex")
   Set Nfield = tdfEmployees.CreateField("Country")
   
   idxCountry.Fields.Append Nfield
   tdfEmployees.Indexes.Append idxCountry
   tdfEmployees.Indexes.Refresh
             
   dbsNorthwind.Close

Put this code in either Button’s click event or Form’s load event and you good to go.
Here are the images before and after code execution.