Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Access, DDL and Constraints

From Wiki

Jump to: navigation, search

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

266 Rating: 2.2/5 (18 votes cast)