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.

The procedure in Hands-On 12-6 illustrates how to create a one-to-many relationship between two tables: Titles and Publishers.

©Hands-On 12-6: Creating a One-to-Many Relationship

1. In the current database, create the Titles and Publishers tables and add the fields as shown below:

Table Name

Field Name

Data Type

Size

Titles

TitleId

Text

8

Titles

PubId

Text

4

Titles

Title

Text

100

Titles

Price

Currency

Setting Up Indexes and Table Relationships with ADO | 219 Creating and Manipulating Databases with ADO

Table Name

Field Name

Data Type

Size

Publishers

PubId

Text

4

Publishers

PubName

Text

40

Publishers

City

Text

25

Publishers

Country

Text

25

2. Make TitleId the primary key for Titles and PubId the primary key for Publishers.

3. In the Visual Basic Editor window, choose Insert | Module.

4. In the module's Code window, type the CreateTblRelation procedure shown below.

5. Choose Run | Run Sub/UserForm to execute the procedure.

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

You can view the relationship between the Publishers and Titles tables that was created by the above procedure in the Relationships window available in the Access user interface. To activate this window, switch to the Database window and choose Tools | Relationships or click the Relationships button on the toolbar. When the empty Relationships window appears, choose Relationships | Show Table. In the Show Table window, hold down the Ctrl key and click the Publishers and Titles table names. Click Add, then click Close. You should see the Publishers and Titles tables in the Relationships window linked together with a one-to-many relationship (see Figure 12-2).

Part II

■ i Relationships

Publishers

Tit loi

Piiïld

r"\oo

Titfcld

P-jbNamc

Publd

Qty

Title

Country

Price

;V.|

<

Figure 12-2: Table relationships can be created programmatically by accessing objects in the ADOX library (see the code in the CreateTblRelation procedure in Hands-On 12-6).

Figure 12-2: Table relationships can be created programmatically by accessing objects in the ADOX library (see the code in the CreateTblRelation procedure in Hands-On 12-6).

0 0

Post a comment