ACC2000: How to Create a Jet CHECK Constraint (201888)



The information in this article applies to:

  • Microsoft Access 2000

This article was previously published under Q201888
Advanced: Requires expert coding, interoperability, and multiuser skills.

SUMMARY

The new SQL grammar exposed by Microsoft Jet database engine version 4.0 allows users to specify business rules that can span more than one table. These are called check constraints and allow users to exceed a single validation rule per table. In this article, a procedure is provided that demonstrates creating a table with a CHECK constraint. It also tests this constraint by attempting to add two records, one which conforms to the constraint, and one that intentionally violates the constraint.

MORE INFORMATION

To demonstrate the example procedure, follow these steps:
  1. In a new Microsoft Access 2000 database, create a new module.
  2. On the Tools menu, click References, and make sure the following references are checked:
    • Microsoft ActiveX Data Objects 2.1 Library
    • Microsoft ADO Ext. 2.1 for DDL and Security
  3. In the new module, type the following:
    Sub CreateJetConstraint()
    Dim ADOConnection As New ADODB.Connection
    Dim SQL As String
    Dim ADOXCat As New ADOX.Catalog
     
    On Error GoTo ErrorHandler
    
    'Delete the sample database JetContraint is it already exists.    
    Kill "c:\JetContstraint.mdb"
    
    ' Using ADOX create a new Jet database.
    ADOXCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
     "Source=c:\JetContstraint.mdb"
     
    ' Set the ADO Connection Properties and open the database.
      With ADOConnection
         .Provider = "Microsoft.Jet.OLEDB.4.0"
         .Open "Data Source=c:\JetContstraint.mdb"
    
    ' Create a new table called CreditLimit.
         .Execute "CREATE TABLE CreditLimit (CreditLimit DOUBLE);"
         .Execute "INSERT INTO CreditLimit VALUES (100);"
      End With
     
     ' Create a new table called Customer with a check constraint which 
     ' validates that a new customers Credit Limit does not exceed the 
     ' credit limit in the CreditLimit table.
    
     SQL = "CREATE TABLE Customers (CustId IDENTITY (100, 10), "  
     SQL = SQL & _
      "CFrstNm VARCHAR(10), CLstNm VARCHAR(15), CustomerLimit DOUBLE, "
     SQL = SQL & "CHECK (CustomerLimit <= " & _
      "(SELECT SUM (CreditLimit) FROM CreditLimit)));"
    
     ADOConnection.Execute SQL
     
     ' Add a new record that does not violate the Customers 
     ' check constraint.
     SQL = "INSERT INTO Customers (CLstNm, CFrstNm, CustomerLimit) VALUES "
     SQL = SQL & "('Smith', 'John', 100);"
     ADOConnection.Execute SQL
    
     ' Try to add a second record that violates the Customers check 
     ' constraint and results in an error.
     SQL = "INSERT INTO Customers (CLstNm, CFrstNm, CustomerLimit) VALUES "
     SQL = SQL & "('Jones', 'Bob', 200);"
     ADOConnection.Execute SQL
     
     Exit Sub
     
     ErrorHandler:
    
    'Trap for File not found error.
     If Err = 53 Then
         Resume Next
     End If
    
     MsgBox Error & " Error# " & Err
     Resume Next
     End Sub
    					
  4. Type the following line in the Immediate window:
    CreateJetConstraint
    						
    Note the following error, demonstrating the CHECK constraint is working:
    One or more values are prohibited by the validataion rule 'Check_EC_57C7D4_11D2' set for 'Customers'. Enter a value that the expression for the field can accept. Error# -2147467259
  5. Open the database C:\JetConstraint and open the table Customers. Note that the record for John Smith, which conforms to the constraint, was added, while the record for Bob Jones, which violated the constraint, was not added.

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming KB201888