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

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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 following example has two fields appended to it, creating a multiple-field index.

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.

REFERENCES

For more information about TableDefs, search for "DAO collections," and then "TableDefs" using the Microsoft Access Help Index.

For more information about Indexes, search for "indexes," and then "creating" using the Microsoft Access Help Index.

For information about how to index an existing field in Microsoft Access 2.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q112107
   TITLE     : ACC2: How to Index an Existing Field with DAO

Keywords          : kbprg PgmHowTo MdlDao
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto