来自: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)
- Sub Create_PrimaryKey()
- Dim cat As New ADOX.Catalog
- Dim myTbl As New ADOX.Table
- Dim pKey As New ADOX.Key
- On Error GoTo ErrorHandler
- cat.ActiveConnection = CurrentProject.Connection
- Set myTbl = cat.Tables("tblFilters")
- With pKey
- .Name = "PrimaryKey"
- .Type = adKeyPrimary
- End With
- pKey.Columns.Append "Id"
- myTbl.Keys.Append pKey
- Set cat = Nothing
- Exit Sub
- ErrorHandler:
- If Err.Number = -2147217856 Then
- MsgBox "The 'tblFilters' is open.", _
- vbCritical, "Please close the table"
- ElseIf Err.Number = -2147217767 Then
- myTbl.Keys.Delete pKey.Name
- Resume
- Else
- MsgBox Err.Number & ": " & Err.Description
- End If
- End Sub
(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)
- 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
- 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
(5)Adding a Multiple-Field Index to a Table:
- Sub Add_MultiFieldIndex()
- Dim conn As New ADODB.Connection
- With conn
- .Provider = "Microsoft.Jet.OLEDB.4.0"
- .Open "Data Source=" & CurrentProject.Path & _
- "/Northwind.mdb"
- ' 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
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.
- Dim idx As New ADOX.Index
- For Each idx In tbl.Indexes
- Debug.Print idx.Name
- Next idx
(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.
- Setup:
- Set tbl = cat.Tables("Employees")
- Debug.Print tbl.Indexes.count
- For Each idx In tbl.Indexes
- If idx.PrimaryKey <> True Then
- tbl.Indexes.Delete (idx.Name)
- GoTo Setup
- End If
- Next idx
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)
- Sub CreateTblRelation()
- Dim cat As New ADOX.Catalog
- Dim fKey As New ADOX.Key
- On Error GoTo ErrorHandle
- cat.ActiveConnection = CurrentProject.Connection
- With fKey
- .Name = "fkPubId"
- .Type = adKeyForeign
- .RelatedTable = "Publishers"
- .Columns.Append "PubId"
- .Columns("PubId").RelatedColumn = "PubId"
- End With
- cat.Tables("Titles").Keys.Append fKey
- MsgBox "Relationship was created."
- Set cat = Nothing
- Exit Sub
- ErrorHandle:
- cat.Tables("Titles").Keys.Delete "fkPubId"
- Resume
- End Sub
(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.