Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1181
  • Last Modified:

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 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27398126.html)- 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...
https://filedb.experts-exchange.com/incoming/ee-stuff/8153-sample.mdb

...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
  
  sSQL = "SELECT * FROM [Access_RELATIONS]"
  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
    
skip_relation:
    
    rs.MoveNext
  Loop
  rs.Close
  Set rs = Nothing

  RESTORERELATIONS = True

Exit_This:
  Exit Function

Err_This:
  Resume Exit_This
End Function

Open in new window

Example-A.png
Example-B.png
0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 2
  • 2
1 Solution
 
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: http://msdn.microsoft.com/en-us/library/bb223702%28v=office.12%29.aspx, 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.
0
 
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?
0
 
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.

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now