Referential Integrity Relationships - in vba

dimanche 3 mai 2015

Hi! Hope you're not still fed up with me :p.
I'd like to appeal to your knowledge.
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.
Looking online, I found this code:

Code:

Public Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, foreignTableName As String,  _
foreignFieldName As String) As Boolean


    On Error GoTo ErrHandler


    Dim db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
 
    relationUniqueName = primaryTableName + "_" + primaryFieldName + _
                        "__" + foreignTableName + "_" + foreignFieldName
 
    Set db = CurrentDb()
 
    'Arguments for CreateRelation(): any unique name,
    'primary table, related table, attributes.
    Set newRelation = db.CreateRelation(relationUniqueName, _
                            primaryTableName, foreignTableName)
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    'Add the relation to the database.
    db.Relations.Append newRelation
 
    Set db = Nothing
 
    CreateRelation = True

     
Exit Function

Even with the comments I don't really understand what exactly this code does or doesn't do.
I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
It would be nice if someone explained to me:
- why it's not visible among other relashionships (or is it me?? :confused: maybe, it wasn't the code that was working but the query...)
- how to implement referential integrity
- what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?

If someone has a link to some easy to understand explainations, I'd gladly read them.
Keep in mind that in front of you is a novice. :) Thanks!
Referential Integrity Relationships - in vba

0 commentaires:

Enregistrer un commentaire

Labels