问题描述
我正在尝试对访问 (jet?) 表创建检查约束.
I am trying to create a check constraint on an access (jet?) table.
所以,我用访问权限打开 .mdb 文件,进入查询->在设计视图中创建查询,输入esc,然后菜单->查看->查询,最后输入
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 > 20))
create table X ( a number, check (a > 20))
但访问认为我有字段定义中的语法错误".然而,我不这么认为.因此,我的问题是:是否可以创建具有访问权限的检查约束.如果是这样:如何.
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 语句输入到访问中.我无法想象我必须使用查询-> 设计视图-> 查询-> 视图路由才能做到这一点.我已经习惯了 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.
感谢您的任何意见雷内
推荐答案
这里有一些注意事项.
您可以为 Oracle 创建传递查询(选择菜单查询">SQL 特定">传递")
You can create a Pass-Through query for Oracle (Select menu "Query" > "SQL Specific" > "Pass-Through")
从 Access 2003 开始,您可以选择 SQL Server Compatible Syntax (ANSI 92) (http://office.microsoft.com/en-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 的约束.请参阅 [中级 Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/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 中创建检查约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!