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)
- 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
- 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 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)
- 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
- 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
- Conn. Close
- Set conn = nothing
- Msgbox "New Index (location) was created ."
- 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.
- 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 isMuch 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 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)
- 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
- 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-pair relationship between tables.