如何处理 Access 中插入的引用完整性

发布于 2024-12-04 18:56:15 字数 475 浏览 0 评论 0原文

我正在从事一个从同事那里接手的 Access 项目。存在三个表:rulesoverviewrelationshiprelationship 表有两个字段,每个字段都是链接到其他两个表中主键的外键。我在表单中有一个 rules 表的数据表视图,我可以在其中毫无问题地删除记录。但是,当我尝试将记录插入 rules 表中时,该记录将插入到 rules 表中,但没有匹配的记录插入到 关系表。我选中了“强制引用完整性”,以及“级联更新相关字段”和“级联删除相关记录”。我天真地假设这可以处理插入,但显然我错了。所以我现在想知道处理这个问题的最佳方法 - 我是否为表单的 After Insert 事件编写一些 VBA,将记录相应地插入到 relationship 表中?

I'm working on an Access project that I took over from a co-worker. There are three tables that exist: rules, overview and relationship. The relationship table has two fields, each is a foreign key that links to a primary key in the other two tables. I have a datasheet view of the rules table in a form, where I can delete records with no problems. However, when I try to insert a record into the rules table, the record will be inserted into the rules table, but there is no matching record inserted into the relationship table. I have "Enforce Referential Integrity" checked, as well as "Cascade Update Related Fields" and "Cascade Delete Related Records". I made a naive assumption that this would handle inserts, but clearly I was wrong. So I'm now wondering about the best way to handle this - do I write some VBA for the After Insert event of the form that inserts a record into the relationship table accordingly?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

十二 2024-12-11 18:56:15

通常的方法是使用一个表单将记录插入到基于查询的规则中,该查询包括关系表和允许用户选择相关概述的组合,或者使用以下设置的表单/子表单合适的主/子字段。在 NorthWind 示例数据库中,订单详细信息表是关系表的一个示例,它使用令人厌恶的表内查找反功能,但您可能会得到一些进一步研究的想法。

选项 1 的更详细描述

表格

概述
身份证号
概述

规则
身份证号
规则

关系
RulesID ) 由两个 FK 组成的 PK
OverviewID )

关系

Relations

数据

Data

建议 1 查询设计

请注意,关系中的两个字段都包含在查询中。没有必要显示规则中的 ID,因为它是一个自动编号字段,但为了简单起见,将其包含在此处。

如果删除一行,则两个表中的记录都将被删除。

查询设计

您不能违反引用完整性。您需要先创建所有概述,然后才能工作,或者提供添加概述的不同方法。

Violate Integrity

如果您更新 RulesIDOverviewID,则会出现一条记录添加到关系表,但不添加到规则。

新关系记录

如果您更新 OverviewIDRule,记录将被添加到关系和规则中。

新规则和关系记录

如果您创建连续表单,则可以以更加用户友好的方式获得上述所有内容更多控制。您可以使用组合框来允许用户选择更友好的概述描述,而不是 ID,并且您可以利用 NotInList 事件来添加新的概述。

continuous Form

请注意,到目前为止,这不需要一行代码。这就是 Access 的力量。

The usual way is to either have a form to insert records into rules that is based on a query that includes the relationship table and, say, a combo that allows the user to select the relevant overview, or a form / subform set-up with suitable master / child fields. In the NorthWind sample database, the Order Detail table is an example of your Relationship table, it uses the loathed look-up-in-table anti-feature, but you may get some ideas for further research.

A Much More Detailed Description of Option 1

Tables

Overview
ID
Overview

Rules
ID
Rule

Relationship
RulesID ) PK formed by two FKs
OverviewID )

Relation

Relations

Data

Data

Suggestion 1 Query Design

Note that both fields from Relationship are included in the query. It is not necessary to show ID from rules, because it is an autonumber field, but it is included here for simplicity.

If a row is deleted, records from both tables will be deleted.

Query Design

You cannot violate referential integrity. You will need to have all overviews created before this will work, or provide a different method of adding Overviews.

Violate Integrity

If you update RulesID and OverviewID, a record will be added to the Relationship table, but not to Rules.

New Relationship record

If you update OverviewID and Rule, records will be added to both Relationship and Rules.

New Rule and Relationship records

If you create a continuous form, you have all the above in a much more user-friendly way with more control. You can use a combobox to allow the user to select the more friendly description of overview, rather than the ID and you can take advantage of the NotInList event to add new Overviews.

continuous Form

Note that so far this has not needed a single line of code. That is the power of Access.

温柔戏命师 2024-12-11 18:56:15

如果有问题的键是自动编号 (IDENTITY),并且一个表引用另一个表(通过外键),那么您可以创建一个连接两个表的 VIEW,将其插入到视图,并且自动编号值会自动复制到引用表中。这是一个快速演示:

Sub RulesOverview()
  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & _
    Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE Rules ( " & _
      " ID INTEGER IDENTITY NOT NULL UNIQUE,  " & _
      " Rule VARCHAR(30) NOT NULL UNIQUE " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE TABLE Overview ( " & _
      " OverviewID INTEGER IDENTITY NOT NULL UNIQUE, " & _
      " Overview VARCHAR(30) NOT NULL UNIQUE " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE TABLE Relationship ( " & _
      " RuleID INTEGER NOT NULL " & _
      "    REFERENCES Rules (ID) " & _
      "    ON DELETE CASCADE, " & _
      " OverviewID INTEGER " & _
      "    REFERENCES Overview (OverviewID) " & _
      "    ON DELETE SET NULL, " & _
      " Name VARCHAR(20) NOT NULL, " & _
      " UNIQUE (RuleID, OverviewID) " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE VIEW RulesRelationship AS " & _
      "SELECT Rules.ID, " & _
      "       Rules.Rule, " & _
      "       Relationship.RuleID, " & _
      "       Relationship.Name " & _
      "  FROM Rules INNER JOIN Relationship " & _
      "          ON Rules.ID = Relationship.RuleID;"

      .Execute Sql

      Sql = _
      "INSERT INTO RulesRelationship (Rule, Name) " & _
      "   VALUES ('Don''t run with scissors', " & _
      "           'Initial scissors');"

      .Execute Sql

      Sql = _
      "SELECT * FROM RulesRelationship;"

      Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

If the key in question is an autonumber (IDENTITY) and one table references the other (via a foreign key) then you can create a VIEW joining two tables, insert into the view, and the autonumber value gets automatically copied to the referencing table. Here's a quick demo:

Sub RulesOverview()
  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & _
    Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE Rules ( " & _
      " ID INTEGER IDENTITY NOT NULL UNIQUE,  " & _
      " Rule VARCHAR(30) NOT NULL UNIQUE " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE TABLE Overview ( " & _
      " OverviewID INTEGER IDENTITY NOT NULL UNIQUE, " & _
      " Overview VARCHAR(30) NOT NULL UNIQUE " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE TABLE Relationship ( " & _
      " RuleID INTEGER NOT NULL " & _
      "    REFERENCES Rules (ID) " & _
      "    ON DELETE CASCADE, " & _
      " OverviewID INTEGER " & _
      "    REFERENCES Overview (OverviewID) " & _
      "    ON DELETE SET NULL, " & _
      " Name VARCHAR(20) NOT NULL, " & _
      " UNIQUE (RuleID, OverviewID) " & _
      ")"

      .Execute Sql

      Sql = _
      "CREATE VIEW RulesRelationship AS " & _
      "SELECT Rules.ID, " & _
      "       Rules.Rule, " & _
      "       Relationship.RuleID, " & _
      "       Relationship.Name " & _
      "  FROM Rules INNER JOIN Relationship " & _
      "          ON Rules.ID = Relationship.RuleID;"

      .Execute Sql

      Sql = _
      "INSERT INTO RulesRelationship (Rule, Name) " & _
      "   VALUES ('Don''t run with scissors', " & _
      "           'Initial scissors');"

      .Execute Sql

      Sql = _
      "SELECT * FROM RulesRelationship;"

      Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文