Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

You will find a good deal on DDL (Data Definition Language) In Intermediate Microsoft Jet SQL for Access 2000, including sample code for creating a constraint that references another table, to quote:

  CREATE TABLE tblCreditLimit (LIMIT DOUBLE)

  INSERT INTO tblCreditLimit VALUES (100)

  ALTER TABLE tblCustomers
     ADD COLUMN CustomerLimit DOUBLE

  ALTER TABLE tblCustomers
     ADD CONSTRAINT LimitRule
     CHECK (CustomerLimit <= (SELECT LIMIT
        FROM tblCreditLimit))

  UPDATE TABLE tblCustomers
     SET CustomerLimit = 200
     WHERE CustomerID = 1

However, this is only available through ADO, you cannot use the above with the query window.

If you are using the table with a form, the error returned will be 3317. You can either accept the default message or supply your own like so:

   Private Sub Form_Error(DataErr As Integer, Response As Integer)
       If DataErr = 3317 Then
           MsgBox "Credit exceeded."
           Response = acDataErrContinue
       End If
   End Sub

Further Information Intermediate Microsoft Jet SQL for Access 2000: http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx