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

Posted on 2011-10-25
Last Modified: 2016-08-29
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

Question by:stephenlecomptejr
    LVL 84
    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.
    LVL 1

    Author Comment

    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?
    LVL 84

    Accepted Solution

    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.

    LVL 1

    Author Closing Comment

    Thanks for your help.   So all I had to do was reverse the data in the LeftTable and RightTable

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now