Adding a Multiple Field Index to a Table

The procedure in Hands-On 12-2 demonstrated adding a single-field index to an existing table by using the ADOX Index object. You can also add a multiple-field index to a table as demonstrated in Hands-On 12-3.

©Hands-On 12-3: Adding a Multiple-Field Index to an Existing Table

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

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

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

Sub Add_MultiFieldIndex()

Dim conn As New ADODB.Connection

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Part II

' Create a multifield Index named Location on City and Region fields. .Execute "CREATE INDEX Location ON Employees (City, Region);" End With conn.Close

Set conn = Nothing

MsgBox "New index (Location) was created." End Sub

The Add_MultiFieldIndex procedure shown above declares the ADO Connection object and opens the connection to the Northwind database. Next, it uses the Execute method of the Connection object to run the DDL (Data Definition Language) CREATE INDEX SQL statement to add an index to the Employees table. Using SQL DDL statements is covered in detail in Part III of this book.

The CREATE INDEX statement has three parts. The name of the index to be created is followed by the keyword ON, the name of the existing table that will contain the index, and the name or names of the fields to be indexed. The field names should be listed in parentheses following the table name. The index is assumed to be ascending unless the DESC keyword is placed at the end of the CREATE INDEX statement.

0 0

Post a comment