How to overcome error 3609 in creating relationships for an Access database via VBA?

Per the following code, I'm trying to accomplish a previous post ( whereby I create relationships based on what's stored in a table...

I get a 3609 error no unique index found for the referenced field of the primary table - where the line Currentdb.Relations.Append oRel is highlighted per Example A. image.

I will also post a sample...

...and was thus wondering how I may change such syntax to make the relationships connect properly.   Otherwise manually I can create a cascading, delete relationship of equal value between reqnum in all three tables per Example B.
Public Function RESTORERELATIONS() As Boolean
On Error GoTo Err_This

  Dim sSQL As String
  Dim rs As DAO.Recordset
  Dim sRelationName As String
  Dim sLeftTable As String
  Dim sLeftCol As String
  Dim sRightTable As String
  Dim sRightCol As String
  Dim sRelationType As String
  Dim oRel As DAO.Relation
  Dim sRelType As String
  If tblExists("ACCESS_Relations") = False Then
    GoTo Exit_This
  End If
  Set rs = CurrentDb.OpenRecordset(sSQL)
  Do Until rs.EOF
    sRelationName = rs.Fields(0)
    sLeftTable = rs.Fields(1)
    sLeftCol = rs.Fields(2)
    sRightTable = rs.Fields(3)
    sRightCol = rs.Fields(4)
    sRelationType = rs.Fields(5)
    'code to restore relationships
    Select Case sRelationType
    Case "4352"
      sRelType = "dbRelationUpdateCascade Or dbRelationDeleteCascade"
      Set oRel = CurrentDb.CreateRelation(sRelationName, sLeftTable, sRightTable, dbRelationUpdateCascade Or dbRelationDeleteCascade)
    Case "256"
      sRelType = "dbRelationUpdateCascade"
      Set oRel = CurrentDb.CreateRelation(sRelationName, sLeftTable, sRightTable, dbRelationUpdateCascade)
    Case "16777218"
      sRelType = "dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade"
      Set oRel = CurrentDb.CreateRelation(sRelationName, sLeftTable, sRightTable, dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
    Case Else
      GoTo skip_relation
    End Select
    If sRelationName <> "MEDEQPROJ_ME" Then
      oRel.Fields.Append oRel.CreateField(sLeftCol)
      oRel.Fields(sLeftCol).ForeignName = sRightCol
      CurrentDb.Relations.Append oRel
    End If
    Set oRel = Nothing
  Set rs = Nothing


  Exit Function

  Resume Exit_This
End Function

Open in new window

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A correction to what I wrote earlier: You must make sure that a Unique Index is set on the Parent field being used in the relationship. It does NOT have to be the primary key field (although proper database design would generally dictate this), but it must be set as Unique.

So ...

tblReq_Codes doesn't have a Unique Index on the reqnum field, but the other two tables do. Those other two tables would be the "Parent" table, and since they have a Unique Index set on their respective reqnum fields, you can use them in the relationship. Notice when you create the relationships in the window they are like this:

One-to-Many : tblREQs to tblREQs-Codes
One-To-Many: tblREQs-Vendors to tblREQs-Codes

And not the "other way" - and this holds true no matter how you attemtp to build the indexes. In this case, the Parent tables are tblREQs and tblREQs-VEndors, both of which have a Unique index set on the reqnum field, which allows Access to construct that relation.

As to why your code is bombing out - a cursory review would seem to indicate that you're not correctly setting the "parent" and "child" tables. You have the "leftable" being tblREQs-Codes, which has no Unique Index set on reqnum, and therefore Access cannot build the relationship.

I'd test it, , but I can't really change the data since the tables are (apparently) part of a Replication scheme and are thus locked.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Error 3609 is the "No unique index found for the referenced field of the primary table" error. According to this link:, you must first make sure that the Parent table's field is the Primary Key of that table. From your last image, it doesn't look as if you have primary key fields being used in those relationships, for example.
stephenlecomptejrAuthor Commented:
Per Example B - I can manually add those relationships across among all reqnum fields in all three tables without the error.   So I don't understand why I can't do so via coding in VBA?

Per the sample - how should I have the keys to allow this error to not come up?
stephenlecomptejrAuthor Commented:
Thanks for your help.   So all I had to do was reverse the data in the LeftTable and RightTable
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.