检查访问限制 [英] Check Constraints in Access

问题描述

我正在处理Access database，我想将Check Constraint保存为查询.在Access中甚至有可能吗? 无论如何，这是我的查询:

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"));

它在AnySQL Maestro中可以使用，但是在Access中不可以使用..

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

推荐答案

Access数据库引擎确实支持CHECK约束，但是无法从Access中的查询设计器执​​行创建约束的DDL.必须使用VBA代码和ADO连接创建它们，如下所示:

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

这篇关于检查访问限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！