Creating a Single Field Index

You can programmatically add an index to a table by using the ADOX Index object.

Before creating an index, make sure the table is not open and that it does not already contain an index with the same name. To define an index, perform the following:

Append one or more columns to the index by using the Append method. Set the Name property of the Index object and define other index properties, if necessary.

Use the Append method to add the Index object to the table's Indexes collection.

Part II

You can use the Unique property of the Index object to specify whether the index keys must be unique. The default value of the Unique property is False.

Another property, IndexNulls, lets you specify whether Null values are allowed in the index. This property can have one of the constants shown in

Table 12-1.

Table 12-1: Intrinsic constants for the IndexNulls property of the ADOX Index object

Constant Name

Description

adIndexNullsAllow

You can create an index if there is a Null value in the index field (an error will not occur).

adIndexNullsDisallow (This is the default value)

You cannot create an index if there is a Null in the index for the column (an error will occur).

adIndexNullsIgnore

You can create an index if there is a Null in the index field (an error will not occur). The Ignore Nulls property in the Indexes window in the user interface will be set to Yes.

adIndexNullslgnoreAny (This value is not supported by the Microsoft Jet Provider)

You can create an index if there is a Null value in the index field. The Ignore Nulls property in the Indexes window in the user interface will be set to No.

The Add_SingleFieldIndex procedure in Hands-On 12-2 demonstrates how to add a single-field index called idxDescription to the table tblFilters.

©Hands-On 12-2: Adding a Single-Field Index to an Existing Table

This procedure uses the tblFilters table (see Hands-On 12-1).

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Add_SingleFieldIndex procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Add_SingleFieldIndex() Dim cat As New ADOX.Catalog Dim myTbl As New ADOX.Table Dim myIdx As New ADOX.Index

On Error GoTo ErrorHandler cat.ActiveConnection = CurrentProject.Connection Set myTbl = cat.Tables("tblFilters")

With myIdx

.Name = "idxDescription" .Unique = False

.IndexNulls = adIndexNullsIgnore .Columns.Append "Description" .Columns(0).SortOrder = adSortAscending End With myTbl.Indexes.Append myIdx

Creating and Manipulating Databases with ADO

Set cat = Nothing Exit Sub

ErrorHandler:

If Err.Number = -2147217856 Then

MsgBox "The 'tblFilters' cannot be open.", vbCritical, _ "Close the table" ElseIf Err.Number = -2147217868 Then myTbl.Indexes.Delete myIdx.Name Resume 0 Else

MsgBox Err.Number & ": " & Err.Description End If End Sub

After setting the index properties, the Description column is appended to the index, and the index sort order is set to the default (adSortAscending). To set the index field's sort order to Descending, use the adSortDescending constant. Next, the index is appended to the Indexes collection of the Table object.

i Indexes: tblFilters

m

T

tare I Field Name I

Sort Order a

Description

Ascending

9

PrimaryKev

Id

M, s cendinq

Index Properties

Primary Unique Ignore Nlfls

No

The name for this hdex, Each index can use up to

10 fields-

No

Yes

Figure 12-1: You can add indexes to an existing table programmatically by using the Key object from the ADOX Object Library. The indexes shown in this screen shot were created by running the procedures in Hands-On 12-1 and 12-2.

Figure 12-1: You can add indexes to an existing table programmatically by using the Key object from the ADOX Object Library. The indexes shown in this screen shot were created by running the procedures in Hands-On 12-1 and 12-2.

0 0

Post a comment