
'----------------------------------------------------------------
' Hands-On 20-1
'----------------------------------------------------------------

Sub CheckColumnValue()
    Dim conn As ADODB.Connection
    Dim strTable As String

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
    strTable = "tblAwards"
    conn.Execute "CREATE TABLE " & strTable & _
        "(Id AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY," & _
        "YearsWorked INT, CONSTRAINT FromTo " & _
        "CHECK (YearsWorked BETWEEN 1 AND 30));"

ExitHere:
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ":" & Err.Description
    Resume ExitHere
End Sub


'----------------------------------------------------------------
' Hands-On 20-2
'----------------------------------------------------------------

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 = "tblSupplies"
    strTable2 = "tblBookOrders"

    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 ('158-76609-09', 5);", _
         adExecuteNoRecords

    conn.Execute "INSERT INTO " & strTable1 & _
        " (ISBN,MaxUnits) " & _
        " Values ('167-23455-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
        MsgBox Err.Number & ":" & Err.Description
        Exit Sub
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 20-3
' No Code.
' Please follow the instructions in the book.
'----------------------------------------------------------------


'----------------------------------------------------------------
' Hands-On 20-4
'----------------------------------------------------------------

Sub RelateTables()
    Dim conn As ADODB.Connection
    Dim strPrimaryTbl As String
    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


'----------------------------------------------------------------
' Hands-On 20-5
' No procedure code.
' Statements to be entered in the Data Definition Query window.
'----------------------------------------------------------------

CREATE TABLE myPrimaryTbl(ID COUNTER CONSTRAINT pKey PRIMARY KEY, COUNTRY TEXT(15));

ALTER TABLE myForeignTbl ADD CONSTRAINT Rel FOREIGN KEY(ID) REFERENCES myPrimaryTbl (ID);

ALTER TABLE myForeignTbl DROP CONSTRAINT Rel;

DROP TABLE myPrimaryTbl;

DROP TABLE myForeignTbl;


