Adding a Multiple Field Index to a Table

Use the ADD CONSTRAINT clause and the UNIQUE keyword in the ALTER TABLE statement to add a multiple-field index. The UNIQUE keyword prevents duplicate values in the index.

©Hands-On 19-11: Adding a Unique Index Based on Two Fields to an Existing Table

This hands-on uses the tblSchools table created in Hands-On 19-2.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the AddMulti_UniqueIndex procedure as shown below.

Programming with the Jet Data Definition Language

Sub AddMulti_UniqueIndex()

Dim conn As ADODB.Connection Dim strTable As String Dim strCol As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strCol = "SchoolId, District"

conn.Execute "ALTER TABLE " & strTable & _ " ADD CONSTRAINT multiIdx UNIQUE " & _ "(" & strCol & ");" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

Indexes: tblSchools

1

Index Name

Field Name

5ort Order

m

T

multHdx

Schoolld

Ascending

il

Oistrict

Ascending

_m\

z

pKey

Schooltd

Ascending

V:i

Index Prope/ties

1 Primary

No

1 Unique

Ves

The name

for this index. Each index

can use

1 Ignore Nulls

No

up to 10 fields.

L

Figure 19-4: After running the procedures in Hands-On 19-10 and 19-11, the tblSchools table contains a primary key and a unique index based on two fields.

Figure 19-4: After running the procedures in Hands-On 19-10 and 19-11, the tblSchools table contains a primary key and a unique index based on two fields.

0 0

Post a comment