VBA/Excel/Access/Word/Access/Column Constraint
Creating a Table with a Validation Rule Referencing a Column in Another Table
<source lang="vb">
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
</source>
Relating Two Tables and Setting up Cascading Referential Integrity Rules
<source lang="vb">
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
</source>
Using a CHECK Constraint to Specify a Condition for All Values Entered for the Column
<source lang="vb">
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
</source>