在访问和/或DAO中创建检查约束 [英] Create a check constraint in access and/or DAO

问题描述

我试图在访问（喷射？）表上创建一个检查约束。

I am trying to create a check constraint on an access (jet?) table.

因此，我打开具有访问权限的.mdb文件，进入查询- >在设计视图中创建查询，
键入esc，然后菜单-> view-> query，最后键入

So, I open the .mdb file with access, go into queries->create query in design view, type esc, then menu->view->query and finally type

创建表X（
a号，
支票（a> 20）
）

create table X ( a number, check (a > 20) )

但access认为我在字段定义中存在语法错误。但是，我不这么认为。因此，我的问题是：是否可以创建具有访问权限的检查约束。

but access thinks that I have a "syntax error in field definition". However, I don't think so. Therefore my question: is it possible to create a check constraint with access. If so: how.

此外，我想使用dao / vba创建约束，而不是在GUI上。

Additionally, I'd like to create the constraint with dao/vba, not on the GUI. Is that possible?

最后，在一个稍微相关的注释上：如何在访问中输入sql语句。我无法想象必须使用querys-> design view-> query-> view route来做到这一点。我已经习惯了Oracle的SQL * Plus，我非常喜欢它，并且希望也可以进行类似的访问。

And lastly, on a slightly related note: how do you enter sql statements into access. I can't imagine that I have to use the queries->design view->query->view route in order to do that. I am used to Oracle's SQL*Plus, which I like very much, and I'd hope there's something similar for access as well.

感谢任何输入
Rene

Thanks for any input Rene

推荐答案

以下是一些说明。

您可以创建Oracle的直通查询（选择菜单查询> SQL特定>直通）

You can create a Pass-Through query for Oracle (Select menu "Query" > "SQL Specific" > "Pass-Through")

自Access 2003开始，您可以选择与SQL Server兼容语法（ANSI 92）（ http://office.microsoft.com/zh- us / access / HA010345621033.aspx ）

Since Access 2003, you can select SQL Server Compatible Syntax (ANSI 92) (http://office.microsoft.com/en-us/access/HA010345621033.aspx)

带有VBA / DAO的验证规则

A validation rule with VBA / DAO

''Reference: Microsoft DAO x.x Object Library

Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb

Set tdf = db.TableDefs("Table1")

tdf.Fields("aDouble").ValidationRule = "<10"
tdf.Fields("aDouble").ValidationText = "Must be less than 10"

受ADO / VBA约束。请参阅[Access 2000的中级Microsoft Jet SQL]（ http ：//msdn.microsoft.com/zh-cn/library/aa140015（office.10）.aspx）

Constraints with ADO / VBA. See [Intermediate Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx)

''Reference: Microsoft ADO Ext. x.x for DDL and Security

Dim cn As ADODB.Connection 'For action queries
Dim rs As ADODB.Recordset  'For select queries
Dim s As String
Dim RecordsAffected As Long

Set cn = CurrentProject.Connection

''You can store sql in a table
s = DLookup("SQLText", "sysSQL", "ObjectName='q1'")
''Result: CREATE TABLE tblCreditLimit (LIMIT DOUBLE)
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can run queries from VBA
s = "INSERT INTO tblCreditLimit VALUES (100)"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "CREATE TABLE tblCustomers (CustomerID COUNTER, CustomerName Text(50))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "INSERT INTO tblCustomers VALUES (1, 'ABC Co')"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "ALTER TABLE tblCustomers " _
   & "ADD COLUMN CustomerLimit DOUBLE"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can add contraints using ADO like so:
s = "ALTER TABLE tblCustomers " _
   & "ADD CONSTRAINT LimitRule " _
   & "CHECK (CustomerLimit <= (SELECT LIMIT " _
   & "FROM tblCreditLimit))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 200 " _
   & "WHERE CustomerID = 1"
''Error occurs here
cn.Execute s, RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 90 " _
   & "WHERE CustomerID = 1"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''Clean up
''You cannot do this through the database window,
''because of the constraint.
s = "ALTER TABLE tblCustomers DROP CONSTRAINT LimitRule "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCustomers "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCreditLimit "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

这篇关于在访问和/或DAO中创建检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！