求教：级联更新和级联删除

jsfrong

请求指点：

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

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

一点通

[转贴]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

谢谢。我的问题是在查询设计视图中，比如我打算建两张表，语句分别如下：


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

查ACCESS的帮助主题：Constraint子句

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

on update cascade| set null

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

wwwwa

在ACCESS2000中似乎不行，在ACCESS2003中设置成SQL92标准才行