ACCESS VBA - 不能创建关系 [英] ACCESS VBA - cannot create relations
问题描述
那么看看下面的例子:
Sub CreateRelation()
Dim db As Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
Set rel = db.CreateRelation("OrderID", "Orders", "Products")
'referential integrity
rel.Attributes = dbRelationUpdateCascade
'specify the key in the referenced table
Set fld = rel.CreateField("OrderID")
fld.ForeignName = "OrderID"
rel.Fields.Append fld
db.Relations.Append rel
End Sub
我不断收到错误:
I keep getting the error:
没有找到主表的引用字段的唯一索引。
No unique index found for the referenced field of the primary table.
如果我包括在这之前的子创建索引赛场上的VBA,它给我的错误:
If I include the vba before this sub to create in index on the field, it gives me the error:
指数已经存在了。
所以我试图找出了这一点。如果没有设置任何主键,将导致此不工作?我被这个困惑,但我真的想弄清楚这一点。因此, ORDERID
是产品外键
表格
So I am trying to figure this out. If there are not any primary keys set, will that cause this to not work? I am confused by this, but I really really want to figure this out. So orderID
is a FOREIGN KEY in the Products
Table
推荐答案
您可以创建通过界面手动的关系,然后进行反向工程了。下面是一个即时窗口会在那里我考察现有的关系:
You can create a relationship manually through the interface, then reverse engineer it. Here is an Immediate Window session where I examined an existing relationship:
? currentdb.Relations.Count
1
? currentdb.Relations(0).Name
parentchild
? currentdb.Relations(0).Table
tblParent
? currentdb.Relations(0).ForeignTable
tblChild
? currentdb.Relations(0).Fields.Count
1
? currentdb.Relations(0).Fields(0).Name
id
? currentdb.Relations(0).Fields(0).ForeignName
parent_id
有关参考,这是用于创建该关系的子
For reference, this is the sub used to create that relationship:
Public Sub CreateRelationship()
Dim strSql As String
strSql = "ALTER TABLE tblChild" & vbNewLine & _
"ADD CONSTRAINT parentchild" & vbNewLine & _
"FOREIGN KEY (parent_id) REFERENCES tblParent (id);"
CurrentProject.Connection.Execute strSql
End Sub
不要与dbRelationUpdateCascade属性打扰,直到你确认后,你已经整理出哪些是外部表和关键。
Don't bother with the dbRelationUpdateCascade attribute until after you confirm you have sorted out which are the foreign table and key.
这篇关于ACCESS VBA - 不能创建关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!