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.
Access, DDL and Constraints
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