Office中国论坛/Access中国论坛

标题: 求教：级联更新和级联删除 [打印本页]

作者: jsfrong 时间: 2006-4-2 18:08
标题: 求教：级联更新和级联删除
请求指点：

ACCESS中级联更新和级联删除的SQL语法格式，SQL SERVER中的类似语法格式无法在ACCESS中套用。如：

on delete cascade、on update cascade、on delete no action、on update no action

作者: 一点通 时间: 2006-4-2 18:31
[转贴]ACCESS911

Sub CreateRelationX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim tdfNew As TableDef
Dim idxNew As Index
Dim relNew As Relation
Dim idxLoop As Index

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

With dbsNorthwind
      ' Add new field to Employees table.
      Set tdfEmployees = .TableDefs!Employees
      tdfEmployees.Fields.Append _
         tdfEmployees.CreateField("DeptID", dbInteger, 2)

      ' Create new Departments table.
      Set tdfNew = .CreateTableDef("Departments")

      With tdfNew
         ' Create and append Field objects to Fields
         ' collection of the new TableDef object.
         .Fields.Append .CreateField("DeptID", dbInteger, 2)
         .Fields.Append .CreateField("DeptName", dbText, 20)

         ' Create Index object for Departments table.
         Set idxNew = .CreateIndex("DeptIDIndex")
         ' Create and append Field object to Fields
         ' collection of the new Index object.
         idxNew.Fields.Append idxNew.CreateField("DeptID")
         ' The index in the primary table must be Unique in
         ' order to be part of a Relation.
         idxNew.Unique = True
         .Indexes.Append idxNew
      End With

      .TableDefs.Append tdfNew

      ' Create EmployeesDepartments Relation object, using
      ' the names of the two tables in the relation.
      Set relNew = .CreateRelation("EmployeesDepartments", _
         tdfNew.Name, tdfEmployees.Name, _
         dbRelationUpdateCascade)

      ' Create Field object for the Fields collection of the
      ' new Relation object. Set the Name and ForeignName
      ' properties based on the fields to be used for the
      ' relation.
      relNew.Fields.Append relNew.CreateField("DeptID")
      relNew.Fields!DeptID.ForeignName = "DeptID"
      .Relations.Append relNew

      ' Print report.
      Debug.Print "

roperties of " & relNew.Name & _
         " Relation"
      Debug.Print "  Table = " & relNew.Table
      Debug.Print "  ForeignTable = " & _
         relNew.ForeignTable
      Debug.Print "Fields of " & relNew.Name & " Relation"

      With relNew.Fields!DeptID
         Debug.Print "  " & .Name
         Debug.Print " Name = " & .Name
         Debug.Print " ForeignName = " & .ForeignName
      End With

      Debug.Print "Indexes in " & tdfEmployees.Name & _
         " TableDef"
      For Each idxLoop In tdfEmployees.Indexes
         Debug.Print "  " & idxLoop.Name & _
            ", Foreign = " & idxLoop.Foreign
      Next idxLoop

      ' Delete new objects because this is a demonstration.
      .Relations.Delete relNew.Name
      .TableDefs.Delete tdfNew.Name
      tdfEmployees.Fields.Delete "DeptID"
      .Close
End With

End Sub

作者: jsfrong 时间: 2006-4-2 18:44
谢谢。我的问题是在查询设计视图中，比如我打算建两张表，语句分别如下：

Create Table 职工(
工号 char(6) primary key,
姓名 char(8) not null,
性别 char(2),
年龄 int);

Create Table 产品(
产品号 char(4) primary key,
产品名 text(16) not null,
价格 money,
工号 char(6),
Constraint C1 Foreign Key(工号) References 职工(工号) UpdateCascade'这里有语法错误，如何纠正？
);

作者: jsfrong 时间: 2006-4-2 20:24
查ACCESS的帮助主题：Constraint子句

其语法格式为： on delete cascade| set null

on update cascade| set null

但一写到查询视图中这几个词就有语法问题，真是懊恼！

作者: wwwwa 时间: 2006-4-3 18:12
在ACCESS2000中似乎不行，在ACCESS2003中设置成SQL92标准才行

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)