ACC2: How to Index an Existing Field with DAO (112107)


The information in this article applies to:

  • Microsoft Access 2.0
This article was previously published under Q112107

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to create a compound (multiple- field) index in an existing table using data access objects (DAO).

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual, Chapter 3, "Introducing Access Basic."

MORE INFORMATION

An Index object contains the fields that are being indexed, and usually contains only a single field. The Index object created in the example below will have two fields appended to it, creating a multiple- field index.

The example below demonstrates how to create a multiple- field index with DAO:
  1. Open the sample database NWIND.MDB.
  2. Create the following new table: 
        Table: Interviews
    --------------------------
    Field Name: CustomerID
       Data Type: Number
       Field Size: Long Integer
    Field Name: InterviewerID
       Data Type: Number
       Field Size: Long Integer
    Field Name: Appointment
       Data Type: Date/Time
  3. Save the table as Interviews without creating a primary key, and then close it.
  4. Create a new module and type the following line in the Declarations section:

    Option Explicit
  5. Type the following function in the module: 
           '---------------------------------------------------------------
       'PURPOSE: Adds a multiple-field index to a table.
       'ACCEPTS: Nothing.
       'RETURNS: Nothing.
       '---------------------------------------------------------------
       Function AddMultiIndex ()
          Dim DB As Database, TDef As TableDef
          Dim Idx As Index, Fld As Field

          Set DB = DBEngine.Workspaces(0).Databases(0)

          ' Open the table definition.
          Set TDef = DB.TableDefs("Interviews")

          ' Create an index called PrimaryKey for this TableDef
          ' .. and turn on the Primary and Required properties.
          Set Idx = TDef.CreateIndex("PrimaryKey")
          Idx.Primary = True
          Idx.Required = True
          Idx.Ignorenulls = False

          ' Create an index field with the same name as a table field,
          ' .. then append it to the index.
          Set Fld = Idx.CreateField("CustomerID")
          Idx.fields.Append Fld

          '   Do the second field the same way.
          Set Fld = Idx.CreateField("InterviewerID")
          Fld.Attributes = DB_DESCENDING
          Idx.fields.Append Fld

          '   Append the index to the TableDef.
          TDef.indexes.Append Idx

       End Function
  6. Save the module as DAOaddMultiIndex.
  7. From the View menu, choose Immediate Window.
  8. Type the following in the Immediate window, and then press ENTER:

    ? AddMultiIndex()
  9. Close the Immediate window and the module, and then open the Interviews table in Design view. Note the compound primary key on the CustomerID and InterviewerID fields.

REFERENCES

For more information about Index objects, search for "Index," and then "Index Object, Indexes Collection" using the Microsoft Access Help menu.
Modification Type:MajorLast Reviewed:11/6/2000
Keywords:kbhowto kbprogramming KB112107
©2002 Microsoft Corporation. All rights reserved.