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

Source: Internet
Author: User
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 ()
  2. Dim cat as new ADOX. Catalog
  3. Dim mytbl as new ADOX. Table
  4. Dim pkey as new ADOX. Key
  5. On Error goto errorhandler
  6. Cat. activeconnection = currentproject. Connection
  7. Set mytbl = cat. Tables ("tblfilters ")
  8. With pkey
  9. . Name = "primarykey"
  10. . Type = adkeyprimary
  11. End
  12. Pkey. Columns. APPEND "ID"
  13. Mytbl. Keys. append pkey
  14. Set cat = nothing
  15. Exit sub
  16. Errorhandler:
  17. If err. Number =-2147217856 then
  18. Msgbox "The 'tblfilters 'is open .",_
  19. Vbcritical, "Please close the table"
  20. Elseif err. Number =-2147217767 then
  21. Mytbl. Keys. Delete pkey. Name
  22. Resume
  23. Else
  24. Msgbox err. Number & ":" & err. Description
  25. End if
  26. 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 ()
  2. Dim cat as new ADOX. Catalog
  3. Dim mytbl as new ADOX. Table
  4. Dim myidx as new ADOX. Index
  5. On Error goto errorhandler
  6. Cat. activeconnection = currentproject. Connection
  7. Set mytbl = cat. Tables ("tblfilters ")
  8. With myidx
  9. . Name = "idxdescription"
  10. . Unique = false
  11. . Indexnulls = adindexnullsignore
  12. . Columns. APPEND "description"
  13. . Columns (0). sortorder = adsortascending
  14. End
  15. Mytbl. Indexes. append myidx
  16. Set cat = nothing
  17. Exit sub
  18. Errorhandler:
  19. If err. Number =-2147217856 then
  20. Msgbox "The 'tblfilters 'cannot be open.", vbcritical ,_
  21. "Close the table"
  22. Elseif err. Number =-2147217868 then
  23. Mytbl. Indexes. Delete myidx. Name
  24. Resume 0
  25. Else
  26. Msgbox err. Number & ":" & err. Description
  27. End if
  28. End sub

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

  1. Sub add_multifieldindex ()
  2. Dim conn as new ADODB. Connection
  3. With Conn
  4. . Provider = "Microsoft. Jet. oledb.4.0"
  5. . Open "Data Source =" & currentproject. Path &_
  6. "/Northwind. mdb"
  7. 'Create a multifield index named location on city and region fields.
  8. . Execute "create index location on employees (city, region );"
  9. End
  10. Conn. Close
  11. Set conn = nothing
  12. Msgbox "New Index (location) was created ."
  13. 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
  2. For each idx in TBL. Indexes
  3. Debug. Print idx. Name
  4. 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 ")
  3. Debug. Print TBL. Indexes. Count
  4. For each idx in TBL. Indexes
  5. If idx. primarykey <> true then
  6. TBL. Indexes. Delete (idx. Name)
  7. Goto setup
  8. End if
  9. 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 ()
  2. Dim cat as new ADOX. Catalog
  3. Dim fkey as new ADOX. Key
  4. On Error goto errorhandle
  5. Cat. activeconnection = currentproject. Connection
  6. With fkey
  7. . Name = "fkpubid"
  8. . Type = adkeyforeign
  9. . Relatedtable = "Publishers"
  10. . Columns. APPEND "pubid"
  11. . Columns ("pubid"). relatedcolumn = "pubid"
  12. End
  13. Cat. Tables ("titles"). Keys. append fkey
  14. Msgbox "relationship was created ."
  15. Set cat = nothing
  16. Exit sub
  17. Errorhandle:
  18. Cat. Tables ("titles"). Keys. Delete "fkpubid"
  19. Resume
  20. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.