Creating a Table with a Validation Rule Referencing a Column in Another Table : Column Constraint « Access « VBA / Excel / Access / Word

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

Creating a Table with a Validation Rule Referencing a Column in Another Table

 

Sub ValidateAgainstCol_InAnotherTbl()
    Dim conn As ADODB.Connection
    Dim strTable1 As String
    Dim strTable2 As String
    Dim InTrans As Boolean

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
    strTable1 = "myBook"
    strTable2 = "myOrders"

    conn.Execute "BEGIN TRANSACTION"
    InTrans = True
    conn.Execute "CREATE TABLE " & strTable1 & _
        "(ISBN CHAR CONSTRAINT PrimaryKey PRIMARY KEY,MaxUnits LONG);", adExecuteNoRecords

    conn.Execute "Insert INTO " & strTable1 & " (ISBN,MaxUnits) Values ('999-99999-09', 5);", adExecuteNoRecords

    conn.Execute "INSERT INTO " & strTable1 & " (ISBN,MaxUnits) Values ('333-55555-69', 7);", adExecuteNoRecords

    conn.Execute "CREATE TABLE " & strTable2 & _
        "(OrderNo AUTOINCREMENT CONSTRAINT" & _
        "PrimaryKey PRIMARY KEY," & _
        "ISBN CHAR, Items LONG," & _
        "CONSTRAINT OnHandConstr CHECK" & _
        "(Items <(Select MaxUnits from " & strTable1 & _
        " WHERE ISBN=" & strTable2 & ".ISBN)));", _
        adExecuteNoRecords
    conn.Execute "COMMIT TRANSACTION"
    InTrans = False
   Application.RefreshDatabaseWindow
ExitHere:
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    If InTrans Then
        conn.Execute "ROLLBACK TRANSACTION"
        Resume ExitHere
    Else
        Debug.Print Err.Number & ":" & Err.Description
        Exit Sub
    End If
End Sub

 

Related examples in the same category