Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Index an Existing Field with Data Access Objects (DAOs)

ACC2000: How to Index an Existing Field with Data Access Objects (DAOs)

View products that this article applies to.

This article was previously published under Q210174
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

This article shows you how to use Data Access Objects (DAOs) to create a compound (multiple-field) index in an existing table.

↑ Back to the top

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.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

  1. Start Microsoft Access and open any database.
  2. In the Database window, click Tables, and then click New to create the following new table in Design View:
    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. In the Database window, click Modules, click New to create a new module, and then type the following procedure:
    '---------------------------------------------------------------
'PURPOSE: Adds a multiple-field index to a table.
'ACCEPTS: Nothing.
'RETURNS: Nothing.
'---------------------------------------------------------------

Function AddMultiIndex()
   Dim dbs As DAO.Database, tdf As DAO.TableDef
   Dim idx As DAO.Index, fld As DAO.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 Immediate 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.

↑ Back to the top

References

For more information about TableDefs, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type createtabledef method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about Indexes, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type indexes collection in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB210174, kbprogramming, kbhowto

↑ Back to the top

Article Info
Article ID : 210174
Revision : 2
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 446