HandsOn Relating Two Tables and Setting up Cascading Referential Integrity Rules

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the RelateTables procedure as shown below.

Sub RelateTables()

Dim conn As ADODB.Connection Dim strPrimaryTbl As String

Part III

Dim strForeignTbl As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strPrimaryTbl = "tblProduct_Orders" strForeignTbl = "tblOrder_Details"

conn.Execute "CREATE TABLE " & strPrimaryTbl & _

"(InvoiceId CHAR(15), PaymentType CHAR(20), " &_ " PaymentTerms CHAR(25), Discount LONG, " &_ " CONSTRAINT PrimaryKey PRIMARY KEY (InvoiceId));", _ adExecuteNoRecords conn.Execute "CREATE TABLE " & strForeignTbl & _ "(InvoiceId CHAR(15), ProductId CHAR(15), " &_ " Units LONG, Price MONEY, " & _

"CONSTRAINT PrimaryKey PRIMARY KEY (InvoiceId, ProductId), " &_ "CONSTRAINT fkInvoiceId FOREIGN KEY (InvoiceId) " &_ "REFERENCES " & strPrimaryTbl & _ " ON UPDATE CASCADE ON DELETE CASCADE);", _ adExecuteNoRecords

Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

The RelateTables procedure in Hands-On 20-4 creates and joins two tables. A Primary Key table named tblProduct _Orders is created with a primary key on the InvoiceId field. The Foreign Key table named tblOrder_Details is created with a multi-field primary key index based on the ProductId and InvoiceId fields. The REFERENCES clause specifies the tblProduct_Orders table as the parent table. The created relationship has the referential integrity rules enforced via the ON UPDATE CASCADE and ON DELETE CASCADE statements.

The outcome of the RelateTables procedure in Hands-On 20-4 is illustrated in the following figures. Figure 20-6 displays the one-to-many relationship between tblProduct_Orders and tblOrder_Details. Figure 20-7 presents the Edit Relationships window in which both cascading updates and deletes are selected.

Programming with the Jet Data Definition Language

Programming with the Jet Data Definition Language

Figure 20-6: To access the Relationships window, choose Tools | Relationships.
Figure 20-7: To access the Edit Relationships window, choose Relationships | Edit Relationship.
0 0

Post a comment