How to Get ForeignTable Field and Relationship Types using VBA

[Bilbo_Baggins_Esq]

Hi All and sorry to bother you with what may prove to be rather obvious to you but seems to be escaping me.

For reasons I need not get into as it generally proves to be a distraction, I need to document the relationships in databases.

I've been playing around with .Relations with some success.
I can easily get the relationship's name, Table Name, Table Field, and ForeignTable.

For i = 0 To objDestDB.Relations.Count - 1
    With rsMigrateRels
        .AddNew
        !RelNumber = i
        !RelAttributes = objDestDB.Relations(i).Attributes
        !RelFields = objDestDB.Relations(i).Fields.Count
        !RelFieldsName = objDestDB.Relations(i).Fields(0).Name
        !RelForeignTable = objDestDB.Relations(i).ForeignTable
        !RelName = objDestDB.Relations(i).Name
        !RelPartialReplica = objDestDB.Relations(i).PartialReplica
        !RelProperties = objDestDB.Relations(i).Properties.Count
        !RelTable = objDestDB.Relations(i).Table
        .Update
    End With
Next i

However, despite lots of digging into the other properties and attributes associated here, I cannot yet figure out how or where to get the:
ForeignTable field
The type of relationship (1:1,1:many,many:many)

I have looked at the Access Developers reference and searched the internet.
The Dev Ref only has a listing for “,Relations” with no detailed reference for the properties and no examples.
When I search the internet I find a ton of references to actually create relationships, but so far zero listings for just reading them.

 

[jdraw]

Have you seen this link and the various examples? It may not do what you want, but may give you some ideas.

 

[Bilbo_Baggins_Esq]

Hi jdraw,

Yes, I’ve read through that page.
It has been very helpful in the past, but if there is a clue on this challenge, I must be missing it.

I appear to have figured out at least part of the puzzle.
I can get the name of the Foreign Table’s field using:
objDestDB.Relations(i).Fields(0).ForeignName

It also dawned on me that I could query the MSysRelationships table.
However, even there, there is no clue available as to the type of relationship.
All I can get there is what I’ve already figured out.
Primary Table & Primary Field
Secondary Table & Secondary Field

So, part of my quest still continues; “How do I find out what type any given relationship is?”

 

[jdraw]

Here's a couple of moe links that may help:
http://www.allenbrowne.com/AppRelReport.html
http://www.allenbrowne.com/AppRelReportCode.html

I found these after some google attempts -- I have not seen the report output
nor have I tested any code. I'd like to know how Access determines the 1-1, 1-Many..

Also found this
You can access the relationships through the mSysObjectsTable as follows:

SELECT MsysObjects.Name, MsysObjects.DateCreate
FROM MsysObjects
WHERE ((Left([Name],4)<>"MSys") AND ((MsysObjects.Type)=8))
ORDER BY MsysObjects.Name;

I did find this is one exchange

Wayne,

Thanks for the reply.

What I am trying to achieve is to understand how Access handles the relationships table, and to have full programmatic control in both reading the table and writing to it.

Based on your response, I was able to do a bit of homework by playing with the object model.

The table MSysRelationships has the following fields that map as follows to the object model (assuming a currentdb of "db" has been declared, and i is an integer fromt 0 to db.relations.count -1):

db.relations(i).name <==> szRelationship
??? <==> icolumn
??? <==> ccolumn
db.relations.attributes <==> grbit
db.relations.table <==> szObject
??? <==> szColumn
db.relations.foreigntable <==> szReferencedObject
??? <==> szReferencedColumn

although the table presents the fields in alphabetical order. The table has no PK, but the combination of the szRelationship and icolumn fields uniquely identify a row.

and this one and the links from this page
http://msdn.microsoft.com/en-us/library/office/dn142315(v=office.15).aspx

 

[vbaInet]

It seems it's the Attributes that gives you this information. It's returns a RelationAttributeEnum listed here:

http://msdn.microsoft.com/en-us/library/office/ff834499(v=office.15).aspx

I've been able to match almost all of them except dbRelationLeft (1:many) which the Attributes property is returning as 16777218, but if I use the Immediate Window to check the Integer value of ?dbRelationLeft it's coming up as 16777216 - same as what is stated on the site. Very odd!

Perhaps you guys can check to see what's yours.

 

[jdraw]

@vbaInet

Thanks.

 

[Bilbo_Baggins_Esq]

Hmmm, Thanks again jdraw.
I'll have to soak through this on Monday.
I haven't previously seen any of it per se (although I did already know how to get the Relationships out of MSysObjects).

Thanks again and I'll let you know what I find out.

 

[vbaInet]

Ah, I get it now. It's a culmination of all the properties. For example if you have Cascade Delete and Cascade Update on, the Attribute will return:

dbRelationDeleteCascade(4096) + dbRelationUpdateCascade(256) = 4352

So you need to subtract each attribute until you get zero. Interesting combinations exercise for you.

 

[Bilbo_Baggins_Esq]

Interesting stuff too vbaInet!

But here too, I am out time for today and will pick this back up on Monday.
I can, in the man time tell you that the attribute values I have forthe 5 realtionships I've tested are:
RelAttributes****RelName******************************Actual Type
16781312******{59DD3A18-D003-4B19-B3DB-15C890E53AD7}**1 to Many
16781312******{B9A569B3-DDDF-47C6-A9CB-9373B23A8E80}**1 to Many
16781312******{F4B18B92-99C9-4B08-96A1-83EDCE8336F9}***1 to Many
2*************FRM_BASEFRM**************************1 to 1
0*************FRM_BASEFRM_SDI***********************1 to Many

 

[vbaInet]

There you are! And I think there's a MapName table that returns the GUID for each table. I'll tell you the name of the table later.

 

[vbaInet]

The name of the table is MSysNameMap.

 

[vbaInet]

RelAttributes****RelName******************************Actual Type
16781312******{59DD3A18-D003-4B19-B3DB-15C890E53AD7}**1 to Many
16781312******{B9A569B3-DDDF-47C6-A9CB-9373B23A8E80}**1 to Many
16781312******{F4B18B92-99C9-4B08-96A1-83EDCE8336F9}***1 to Many
2*************FRM_BASEFRM**************************1 to 1
0*************FRM_BASEFRM_SDI***********************1 to Many

It looks like you still have to do that subtraction exercise to get any of the other attributes like Cascade Update etc. It's actually much simpler than I anticipated:

dbRelationUnique - 1
dbRelationLeft - 16777216
dbRelationRight - 33554432

So the conditions will be:

Right Join (dbRelationRight) - Is Greater than 33554431
Left Join (dbRelationLeft) - Is Between 16777216 And 20000000
One-To-One (dbRelationUnique) - Is Less Than 16777216 Or just Less Than 4500 (because that's the highest it can go if you add up all the other attributes)

 

[jdraw]

I looked at a new db with just the Navigation tables (MSysNavPaneGroupCategories,MSysNavPaneGroups,MSysNavPaneGroupToObjects) listed. I looked at the relation attributes and saw the 4352 just as vbaInet said.

Bilbo,
I waiting to see some code to put meaning and some simplicity to all this. Let us know how it goes.

 

[vbaInet]

jdraw, what table did you use to get the attributes? I've not really look at the System Tables yet, just code.

 

[jdraw]

I used what I thought was your technique (I've deleted it but as I recall)

-made hidden and system tables available
-immediate window
? Currentdb.Relation(0).Name
? Currentdb.Relation(0).attributes

and same thing with Relation(1).
There were only 2 relations in the database, both 1 to many.

I did see a post in another forum (I think Wayne Phillips) that said the grbit in MSysRelationships is the Attributes. I didn't know the enumerations until you gave that link.

 

[vbaInet]

I used what I thought was your technique (I've deleted it but as I recall)

-made hidden and system tables available
-immediate window
? Currentdb.Relation(0).Name
? Currentdb.Relation(0).attributes

and same thing with Relation(1).
There were only 2 relations in the database, both 1 to many.

It works like this. Two tables named tblParent and tblChild will have a relation called tblParenttblChild so to get the relation you do:
?Currentdb.Relation("tblParenttblChild").Attributes

I did see a post in another forum (I think Wayne Phillips) that said the grbit in MSysRelationships is the Attributes. I didn't know the enumerations until you gave that link.

Yeah I saw that grbit already. I thought that Bilbo's field "Actual Type" (in his last post) came from one of the system tables but I can see it's one he made up.

If there's no referential integrity then so we can't really say that 2 (i.e. dbRelationDontEnforce) is a 1-to-many relationship. With that said I'll adjust my last condition:

One-To-One (dbRelationUnique) - Is Less Than 2 Or Between 3 And 4500

 

[Bilbo_Baggins_Esq]

got a late start today but am working on it.
Will try to post some functional code for this tomorrow

THANKS ALL!!!

 

[Bilbo_Baggins_Esq]

ok guys, I'd bet money there is somebody who frequents these boards laughing their asses off at us....
Include myself cause I can laugh at myself too.

So, I do have some code, and I will post it below, but first a reminder of what the only remaining question I actually have: "What is the relationship type? One-To-One or One-To-Many"

ok, so, it turns out there really is a very simple, laughably simple, way to determine the answer to JUST that question.
If the value for objDestDB.Relations(i).Attributes is even, it is One-To-Many
If the value for objDestDB.Relations(i).Attributes is odd, it is One-To-One

for some reason, as I was looking at all these various attribute values, it did not immediately occur to me that the only ODD valued option was one-to-one.

Therefore, it matters not one iota any of the other attribute options added or not.
At the end, or at the beginning, the only real thing your need to know is if it is odd or even.

I busted out laughing.

 

[Bilbo_Baggins_Esq]

ok, so, since the real solution to the actual question turns out to be really simple (al-be-it derived from a troublesome learning process), there really isn't any need of a detailed code solution.

however, since (for whatever reason) I must have needed to go thorugh the exercise of identification of all the other attributes, I may as well post some code for that piece anyway.

Public Sub RelsTesting()
Dim RelationAttrib As Double
Dim vbRelationRight As Boolean
Dim vbRelationLeft As Boolean
Dim vbRelationEqual As Boolean
Dim vbRelationDeleteCascade As Boolean
Dim vbRelationUpdateCascade As Boolean
Dim vbRelationInherited As Boolean
Dim vbRelationDontEnforce As Boolean
Dim vbRelationUnique As Boolean
Dim MsgText As String

[COLOR="darkgreen"]'dbRelationDeleteCascade = 4096
'dbRelationDontEnforce = 2
'dbRelationInherited = 4
'dbRelationLeft = 16777216
'dbRelationRight = 33554432
'dbRelationUnique = 1
'dbRelationUpdateCascade = 256

‘where objDestDB is a valid Access Database expression[/COLOR]
For i = 0 To objDestDB.Relations.Count - 1
    RelationAttrib = objDestDB.Relations(i).Attributes

[COLOR="darkgreen"]'Determine the Join Type[/COLOR]
    Select Case RelationAttrib
        Case Is >= dbRelationRight
            vbRelationRight = True
            MsgText = "Join Type is Right" & vbCr
            RelationAttrib = RelationAttrib - dbRelationRight
        Case Is >= dbRelationLeft
            vbRelationLeft = True
            RelationAttrib = RelationAttrib - dbRelationLeft
            MsgText = "Join Type is Left" & vbCr
        Case Else
            vbRelationEqual = True
            MsgText = "Join Type is Equal" & vbCr
    End Select

[COLOR="DarkGreen"]'Determine the Cascades[/COLOR]
    If RelationAttrib >= dbRelationDeleteCascade Then
        vbRelationDeleteCascade = True
        MsgText = MsgText & "Cascade Delete Related Records is True" & vbCr
        RelationAttrib = RelationAttrib - dbRelationDeleteCascade
        Else
        MsgText = MsgText & "Cascade Delete Related Records is False" & vbCr
    End If

    If RelationAttrib >= dbRelationUpdateCascade Then
        MsgText = MsgText & "Cascade Update Related Fields is True" & vbCr
        vbRelationUpdateCascade = True
        RelationAttrib = RelationAttrib - dbRelationUpdateCascade
        Else
        MsgText = MsgText & "Cascade Update Related Fields is False" & vbCr
    End If

[COLOR="darkgreen"]'Check if inherited[/COLOR]
    If RelationAttrib >= dbRelationInherited Then
        vbRelationInherited = True
        MsgText = MsgText & "Relationships are Inherited" & vbCr
        RelationAttrib = RelationAttrib - dbRelationInherited
        Else
        MsgText = MsgText & "Relationships are not Inherited" & vbCr
    End If

[COLOR="darkgreen"]'Determine integrity[/COLOR]
    If RelationAttrib >= dbRelationDontEnforce Then
        vbRelationDontEnforce = True
        MsgText = MsgText & "Referential Integrity is enforced" & vbCr
        RelationAttrib = RelationAttrib - dbRelationDontEnforce
        Else
        MsgText = MsgText & "Referential Integrity is not enforced" & vbCr
    End If

[COLOR="darkgreen"]'Determine Relationship[/COLOR]
    If RelationAttrib >= dbRelationUnique Then
        vbRelationUnique = True
        MsgText = MsgText & "Relationship is one-to-one" & vbCr
        Else
        MsgText = MsgText & "Relationship is one-to-many" & vbCr
    End If

    MsgBox "Attributes for:" & vbCr & objDestDB.Relations(i).Name & vbCrLf & MsgText

Next i

End Sub

 

[Bilbo_Baggins_Esq]

Ah, I get it now. It's a culmination of all the properties. For example if you have Cascade Delete and Cascade Update on, the Attribute will return:

dbRelationDeleteCascade(4096) + dbRelationUpdateCascade(256) = 4352

So you need to subtract each attribute until you get zero. Interesting combinations exercise for you.

So, it turns out, this was close.
You havde to back all the attributes out unti get to EITHER 1 or 0.
1 = One-to-One
0 = One-to-Many

so, as I hope I've made plenty visible to anyone else who may follow, it all you really want to know it the type, all you really need to know is odd or even.

If you need the other attributes, there is some code posted above, or there may be some more efficent ways if all you needs is a single attribute.