Microsoft KB Archive/141612

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

Article ID: 141612

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q141612



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



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

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.

 Open the sample database Northwind.mdb.  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  Save the table as Interviews, and then close it. Do not create a primary key.  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  To test this function, type the following line in the Debug window, and then press ENTER.

? AddMultiIndex

 Open the Interviews table in Design view. Note the compound primary key on the CustomerID and InterviewerID fields.</li></ol>

<div class="references_section">