How to handle Referential Integrity for Inserts in Access

Question

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?

Answer 1

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

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.

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.

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

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

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.

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

Answer 2

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