ACC: How to Index an Existing Field with DAO (95/97)

MORE INFORMATION

1. Open the sample database Northwind.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, and then close it. Do not create a primary key.
4. Create a module and type the following procedure:

   
         ' '---------------------------------------------------------------
         'PURPOSE: Adds a multiple-field index to a table.
         'ACCEPTS: Nothing.
         'RETURNS: Nothing.
         '---------------------------------------------------------------
         Function AddMultiIndex ()
            Dim dbs As Database, tdf As TableDef
            Dim idx As Index, fld As Field
   
            Set dbs = CurrentDb()
   
            ' Open the table definition.
            Set tdf = dbs.TableDefs("Interviews")
   
            ' Create an index called PrimaryKey for this TableDef
            ' and turn on the Primary and Required properties.
            Set idx = tdf.CREATEINDEX("PrimaryKey")
            With idx
               .Name = "PrimaryKey"
               .PRIMARY = True
               .Required = True
               .IgnoreNulls = False
            End With
   
            ' 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 = dbDescending
            idx.Fields.Append fld
   
            ' Append the index to the TableDef.
            tdf.Indexes.Append idx
   
         End Function
   						

5. To test this function, type the following line in the Debug window, and then press ENTER.

   ? AddMultiIndex()

6. Open the Interviews table in Design view. Note the compound primary key on the CustomerID and InterviewerID fields.