VBA/Excel/Access/Word/Access/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

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

Using a CHECK Constraint to Specify a Condition for All Values Entered for the Column

 
Sub CheckColumnValue() 
    Dim conn As ADODB.Connection 
    On Error GoTo ErrorHandler 
    Set conn = CurrentProject.Connection 
    conn.Execute "CREATE TABLE myTable(Id AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY," & _ 
        "CountMe INT, CONSTRAINT FromTo CHECK (CountMe BETWEEN 1 AND 30));" 
ExitHere: 
    conn.Close 
    Set conn = Nothing 
    Exit Sub 
ErrorHandler: 
    Debug.Print Err.Number & ":" & Err.Description 
    Resume ExitHere 
End Sub