Create Relationship *Without* enforcing RI

[liamo]

Hi All,

I have a need to create - in code - a set of relationships on tables that have been pre-populated with data. As the tables may contain data that would break Referential Integrity, I need to create the relationship *without* enforcing RI.

This is pretty straightforward to do in the Relationship Designer, ie Simply untick the "Enforce Referential Integrity" checkbox.

My problem is that there doesn't appear to be any way to replicate this in code.

I can set .UpdateRule = adRINone and .DeleteRule = adRINone but I can't find any way to do .EnforceRI = NoThanks

I've done a lot of searching with no success and I'd be most grateful for any suggestions or pointers to URLs or documents.

Thanks

Liam

 

[liamo]

Thank you for your reply.

There is absolutely no reason to establish a relationship if you are not going to enforce RI.

I respectfully, but strongly, disagree.

My question was not about whether or not I should establish a relationship - it was very specific. I want to replicate - in code - something that can be done interactively.

BTW, if your tables already contain bad data why are you continuing to ignore RI? Don't you care that you have bad data in your tables?

In a nutshell : The bad data gets imported from elsewhere every night and its only reason for being in Access is so that it can be reported on. The relationships will be re-created every night which is why they need to be done in code - for automation purposes. I don't have control over the data nor do I care that it's bad. I simply need to create the relationships. As a developer of many years this offends my sense of order and design but this is the real world and if this is what my employers (and their clients) want, that's what they'll get.

Regards,

Liam

 

[liamo]

Hi Pat,

Why do you think that you need formal relationships? Not having defined relationships will not break any of your queries.

Agreed. However, the defined relationships would make it easier for the end-users - who are a bit techno-challenged - when placing tables into a query. The users wouldn't have to set them up themselves.


I don't believe that there is one since there would be no need to establish a relationship if you didn't want to enforce RI.

That was my thinking but I was hoping that someone could prove me wrong.

Thanks for your time.

Regards,

Liam

 

[adam_fleck]

You could try something like this:

The code below works in Access 2000.

'Access 2000
'Code needs reference to DAO 3.6 selected and
'references to ADO 2.x deselected.

Public Function make_rel()

    Dim rel_New As Relation
    Dim rel_name As String, i As Integer
    
   'Not very sound attempt to guarantee the new relationship
   'has a unique name:
   
    i = CurrentDb.Relations.Count
    rel_name = "rel_" & Format$(i, "00")
    
    Set rel_New = CurrentDb.CreateRelation(rel_name, _
                                          "tbl_1", _
                                          "tbl_2", _
                                           dbRelationDontEnforce)
   'dbRelationDontEnforce just draws in the join line
                                                                                                                                                                        
   'the field to draw from : here it's tbl_1_ID
    rel_New.Fields.Append rel_New.CreateField("tbl_1_ID")
    
   'the name of the field we're drawing to in the other table : 
   'here the other field has the same name
    rel_New.Fields("tbl_1_ID").ForeignName = "tbl_1_ID"
    
   'And append the relationship.
    CurrentDb.Relations.Append rel_New

End Function

Hope you get this to work. With Pat, I'm wondering what perhaps subtle benefit there is in doing this. If both tables already have primary keys, then setting a relationship will automatically index the foreign key in the secondary table. But if it's the indexes you want, you could also use .CreateIndex.

 

[Mike375]

I think that Liam is wanting to be able to have someone stick a couple of tables on a query grid and not have them worry about which fields to joing. This would be very applicable if the two fields had different names and doubly so if the "one" side was not a primary key field and as such not highlighted in bold.

I think I might have a similar situation with my business partner. What I did in his case was to preprare some queries and I have macros open them in design view and I have the fields that are likely to have criteria placed on the far left of the query. In this case there are simply too many possibilities to even think about having a form where he could enter criteria in text boxes.

Mike

 

[liamo]

Success!

Adam, many thanks for the snippet. That was just what I needed.

Thanks Pat and Mike for your replies.

Best regards,

Liam