Relating Two Tables and Setting up Cascading Referential Integrity Rules : Column Constraint « Access « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Access
  3. Column Constraint

Relating Two Tables and Setting up Cascading Referential Integrity Rules

 
Sub RelateTables()
    Dim conn As ADODB.Connection
    Dim strPrimaryTbl As String
    Dim strForeignTbl As String

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
    strPrimaryTbl = "myTbl"
    strForeignTbl = "myTbl_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:
    Debug.Print Err.Number & ":" & Err.Description
    Resume ExitHere
End Sub








Related examples in the same category

1.Using a CHECK Constraint to Specify a Condition for All Values Entered for the Column
2.Creating a Table with a Validation Rule Referencing a Column in Another Table