sql - Check Constraints in Access

Question

I'm working on an Access database, and I want to save a Check Constraint as a query. Is that even possible in Access?? Anyhow, this is my Query:

ALTER TABLE LEVERANCIER
ADD CONSTRAINT chk_postcode CHECK(
NOT EXISTS(
SELECT levnr, postcode
FROM LEVERANCIER
WHERE Left(postcode, 4) = 5050 OR Woonplaats = "Amsterdam"));

It does work in AnySQL Maestro, but it doesn't work in Access..

Answer 1

The Access Database Engine does support CHECK constraints, but the DDL to create them cannot be executed from the Query Designer in Access. They must be created using VBA code and an ADO connection, like so:

Option Compare Database
Option Explicit

Public Sub AddCheckConstraint()
    Dim strSql As String
    strSql = _
            "ALTER TABLE LEVERANCIER" & vbNewLine & _
            vbTab & "DROP CONSTRAINT chk_postcode;"
    On Error Resume Next
    CurrentProject.Connection.Execute strSql
    On Error GoTo 0
    strSql = _
            "ALTER TABLE LEVERANCIER" & vbNewLine & _
            vbTab & "ADD CONSTRAINT chk_postcode" & vbNewLine & _
            vbTab & "CHECK (" & vbNewLine & _
            vbTab & vbTab & "NOT EXISTS (" & vbNewLine & _
            vbTab & vbTab & vbTab & "SELECT levnr, postcode " & vbNewLine & _
            vbTab & vbTab & vbTab & "FROM LEVERANCIER " & vbNewLine & _
            vbTab & vbTab & vbTab & "WHERE Left(postcode, 4) = '5050' OR Woonplaats = 'Amsterdam' " & vbNewLine & _
            vbTab & vbTab & ")" & vbNewLine & _
            vbTab & ");"
    CurrentProject.Connection.Execute strSql
End Sub