VBA/Excel/Access/Word/Access/Column Constraint

Материал из VB Эксперт
Перейти к: навигация, поиск

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>