'
Essentially what I'm doing is making copies of tables (templates).

The idea is that there are books. Users can create new books (which is three tables) and then select any book they want.

The tables store Game sessions. When the user deletes old sessions I want the old data in the two linked tables to be deleted (cascade delete)

This is my text-based, multiple-choice game.

I'm having no problems creating the tables or primary keys in them. That's all working.

No matter what I do with the relationships, it fails someplace. This is failing with an error "cannot create relation with no fields defined".

This is the code.

Code:
Sub CreateRelation(ByRef Table1 As String, ByRef Index1 As String, ByRef Table2 As String, ByRef Index2 As String)
Dim Relationship As DAO.Relation
Dim tblDef1 As DAO.TableDef
Dim tblDef2 As DAO.TableDef
Dim idx1 As DAO.Index
Dim idx2 As DAO.Index


With DB

  .TableDefs.Refresh

  Set tblDef1 = .TableDefs(Table1)
  Set tblDef2 = .TableDefs(Table2)

  Set idx1 = tblDef1.Indexes(Index1)
  Set idx2 = tblDef2.Indexes(Index2)

  Set Relationship = .CreateRelation(Index1 & Index2, idx1, idx2, dbRelationDeleteCascade)

  Relationship.Fields.Append Relationship.CreateField(Index1)
  Relationship.Fields.Append Relationship.CreateField(Index2)

  Relationship.Fields(Index1).ForeignName = Index2

  .Relations.Append Relationship

End With

End Sub
This is part of the sub where it gets called.

Code:
Sub ASub

....snip

If CreateTableCopy(BOOK_TITLE_TEMPLATE, BookTitle) <> 0 Then Exit Sub

AddTableIndex BookTitle, "ChapterID", "ChapterID", False, False, True

If CreateTableCopy(POINT_INSTANCES_TEMPLATE, sPointInstances) <> 0 Then Exit Sub

AddTableIndex sPointInstances, "ChapterID", "ChapterID", False, False, True

CreateRelation BookTitle, "ChapterID", sPointInstances, "ChapterID"

If CreateTableCopy(STARTING_ATTRIBUTE_VALUES_TEMPLATE, sStartingAttributeValues) <> 0 Then Exit Sub

CreateRelation BookTitle, "ChapterID", sStartingAttributeValues, "ChapterID"

DB.TableDefs.Refresh

End Sub