VBA brush up 12: setting up indexes and table relationships with ADO

Technorati labels: relationship, access, VBA, index, ADO

From: julitta korol, "access.2003.programming. By. example. With. VBA. xml. And. asp", by wordware Publishing, Inc. 2005, p212-p220

(1)Primary KeyIs an index with its unique and primarykey properties set to true. There can beOnly one primary key per table. A primary keyUniquely 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, useKey object's type property. For example, to create a primary key, set the key object's type propertyAdkeyprimary.

(2)

1. Sub create_primarykey ()
3. Dim cat as new ADOX. Catalog
4. Dim mytbl as new ADOX. Table
5. Dim pkey as new ADOX. Key
7. On Error goto errorhandler
9. Cat. activeconnection = currentproject. Connection
10. Set mytbl = cat. Tables ("tblfilters ")
11. With pkey
12. . Name = "primarykey"
13. . Type = adkeyprimary
14. End
15. Pkey. Columns. APPEND "ID"
16. Mytbl. Keys. append pkey
17. Set cat = nothing
18. Exit sub
20. Errorhandler:
21. If err. Number =-2147217856 then
22. Msgbox "The 'tblfilters 'is open .",_
23. Vbcritical, "Please close the table"
24. Elseif err. Number =-2147217767 then
25. Mytbl. Keys. Delete pkey. Name
26. Resume
27. Else
28. Msgbox err. Number & ":" & err. Description
29. End if
31. End sub

(3)Creating a single-field Index: Before creating an index, make sureThe 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 indexBy using the append method.
• Set the name property of the index objectAnd define other index properties, if necessary.
• Use the append method to add the index object to the table's indexes collection.

You can useUniqueProperty 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)

1. Sub add_singlefieldindex ()
3. Dim cat as new ADOX. Catalog
4. Dim mytbl as new ADOX. Table
5. Dim myidx as new ADOX. Index
6. On Error goto errorhandler
8. Cat. activeconnection = currentproject. Connection
9. Set mytbl = cat. Tables ("tblfilters ")
10. With myidx
11. . Name = "idxdescription"
12. . Unique = false
13. . Indexnulls = adindexnullsignore
14. . Columns. APPEND "description"
15. . Columns (0). sortorder = adsortascending
16. End
17. Mytbl. Indexes. append myidx
19. Set cat = nothing
20. Exit sub
22. Errorhandler:
23. If err. Number =-2147217856 then
24. Msgbox "The 'tblfilters 'cannot be open.", vbcritical ,_
25. "Close the table"
26. Elseif err. Number =-2147217868 then
27. Mytbl. Indexes. Delete myidx. Name
28. Resume 0
29. Else
30. Msgbox err. Number & ":" & err. Description
31. End if
33. End sub

(5)Adding a multiple-field index to a table:

1. Sub add_multifieldindex ()
3. Dim conn as new ADODB. Connection
4. With Conn
5. . Provider = "Microsoft. Jet. oledb.4.0"
6. . Open "Data Source =" & currentproject. Path &_
7. "/Northwind. mdb"
9. 'Create a multifield index named location on city and region fields.
10. . Execute "create index location on employees (city, region );"
11. End
12. Conn. Close
13. Set conn = nothing
14. Msgbox "New Index (location) was created ."
16. End sub

It usesExecuteMethod of the connection object to runDDL(Data Definition Language)Create index SQLStatement to add an index to the Employees table.

The create index statement has three parts.Name of the IndexTo be created is followed by the keywordOn, The name of the existing table that will contain in the index, and the name or names of the fields to be indexed. the Field Names shocould be listed in parentheses following the table name. the index is assumed to be ascending unlessDescKeyword is placed at the end of the create index statement.

(6)Listing indexes in a table:The indexes collectionContains all index objects of a table. You can retrieve all the index names from the indexes collection.

1. Dim idx as new ADOX. Index
3. For each idx in TBL. Indexes
4. Debug. Print idx. Name
5. 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 isMuch faster to remove them programmatically.

1. Setup:
2. Set TBL = cat. Tables ("employees ")
4. Debug. Print TBL. Indexes. Count
5. For each idx in TBL. Indexes
6. If idx. primarykey <> true then
7. TBL. Indexes. Delete (idx. Name)
8. Goto setup
9. End if
10. Next idx

Notice thatEach time you delete an index from the table's indexes collection you must set the reference to the tableBecauseCurrent 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-Define relationship between tables, perform the following steps:

1. UseADOX key objectTo create a foreign key and set the type property of the key objectAdkeyforeign.A foreign key consists of one or more fields in a foreign table that uniquely identify all rows in a primary table.

2. UseRelatedtableProperty 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 "external" side of a one-to-Define relationship and provides a foreign key to another table in a database.

4. SetRelatedcolumnProperty to the name of the corresponding column inThe primary table.

5. UseAppendMethod to add the foreign keyTo the keys collectionOf the table containing the primary key.

(9)

1. Sub createtblrelation ()
3. Dim cat as new ADOX. Catalog
4. Dim fkey as new ADOX. Key
5. On Error goto errorhandle
7. Cat. activeconnection = currentproject. Connection
8. With fkey
9. . Name = "fkpubid"
10. . Type = adkeyforeign
11. . Relatedtable = "Publishers"
12. . Columns. APPEND "pubid"
13. . Columns ("pubid"). relatedcolumn = "pubid"
14. End
15. Cat. Tables ("titles"). Keys. append fkey
17. Msgbox "relationship was created ."
18. Set cat = nothing
19. Exit sub
21. Errorhandle:
22. Cat. Tables ("titles"). Keys. Delete "fkpubid"
23. Resume
25. 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-pair relationship between tables.