ACC2: How to Index an Existing Field with DAO

MORE INFORMATION

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.