学步园

来自：Julitta Korol, "Access.2003.Programming.by.Example.with.VBA.XML.and.ASP", by Wordware Publishing, Inc. 2005, p212-p220

（1）A primary key is an index with its Unique and PrimaryKey properties set to True. There can be only one primary key per table. A primary key uniquely identifies a row in a table.

To create new keys, use the Key object from the ADOX library. To determine whether the key is primary, foreign, or unique, use the Key object’s Type property. For example, to create a primary key, set the Key object’s Type property to adKeyPrimary.

（2）

（3）Creating a Single-Field Index：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.

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.

（4）

（5）Adding a Multiple-Field Index to a Table:

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.

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.

（6）Listing Indexes in a Table: The Indexes collection contains all Index objects of a table. You can retrieve all the index names from the Indexes collection.

（7）Deleting Table Indexes: Although you can delete unwanted or obsolete indexes from the Indexes window in the Microsoft Office Access 2003 user interface, it is much faster to remove them programmatically.

Notice that each time you delete an index from the table’s Indexes collection you must set the reference to the table because current settings are lost when an index is deleted. Hence, the GoTo Setup statement sends Visual Basic to the Setup label to get the new reference to the Table object.

（8）Creating Table Relationships: To establish a one-to-many relationship between tables, perform the following steps:

1. Use the ADOX Key object to create a foreign key and set the Type property of the Key object to adKeyForeign. A foreign key consists of one or more fields in a foreign table that uniquely identify all rows in a primary table.

2. Use the RelatedTable property to specify the name of the related table.

3. Use the Append method to add appropriate columns in the foreign table to the foreign key. A foreign table is usually located on the “many” side of a one-to-many relationship and provides a foreign key to another table in a database.

4. Set the RelatedColumn property to the name of the corresponding column in the primary table.

5. Use the Append method to add the foreign key to the Keys collection of the table containing the primary key.

（9）

（10）Chapter Summary: In this short chapter you acquired programming skills that enable you to create keys (primary keys and indexes) in Microsoft Access tables. You also learned how to use ADOX to establish a one-to-many relationship between tables.