By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Table Relationships Attributes - 1-to-Many Enforced = 0?

twinnyfo
Expert Mod 2.5K+
P: 3,313
Friends,

This may be a relatively simple question to answer, but I can't think of anywhere else to ask, and I am sure there are some experts out here who might have some insight on this.

If I am examining the attributes associated with my Table Relationship using VBA, I can identify the type of Relationship by using the .Attribute property of the Relations collection.

So, here is the question, when I establish relationships between tables in a One-to-Many, Enforced relationship (a pretty standard configuration for most of my tables), the .Attribute value for that relationship is 0, which to me, gives the impression that there is "no" relationship--or, at least, "no attribute".

When I get a listing of the possible .Attribute values, (thanks to NeoPa for showing me this trick a few years back, I get a list of Attributes which does NOT include either "One-to-Many" nor "Enforced". See the image.

Does this mean that an .Attribute value of 0 is a "One-to-Many, Enforced" relationship, meaning that the "Default" relationship type/attribute is "One-to-Many, Enforced", because only in certain situations would you have it any other way, or is the "One-to-Many, Enforced" relationship just not among the list?

Either way, maybe some background on this would hepp me understand.

Looking forward to all y'all's discussion!
Attached Images
File Type: png RelAttr.png (4.5 KB, 244 views)
2 Weeks Ago #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,561
Hi Twinny.

These are all bitmasks, so all powers of 2.

You'll see the second item is dbRelationDontEnforce. This has a value of 2. The fact that your zero (0) doesn't have this set means that your Relationship is enforced.

I'm not convinced about the One-to-Many aspect of things. I don't see any alternatives in that list so I suspect the .Attribute covers what it covers. No more & no less. The bitmasks indicate fairly clearly what they cover. Other details may be found elsewhere.

I suggest, if you're keen to find out more, you create a bunch of different Relationships (or just one and keep changing it) and then examine .Attribute & possibly other properties of the Relation object, to see what changes where.

Just to check - you can see that in a Enum, when you select one the value in both decimal & hex (where different) appears below so you can see what each does?
2 Weeks Ago #2

ADezii
Expert 5K+
P: 8,668
  1. I threw together a little Demo for you that hopefully will illustrate the Attributes Property along with Bit Masking. Obviously, I modified some characteristics of the Relationships that the Employee Table in the Sample Northwind Database is involved in. Hope this helps.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rel As DAO.Relation
    3. Dim fld As DAO.Field
    4. Dim sTableName As String
    5.  
    6. Set MyDB = CurrentDb
    7. sTableName = "Employees"
    8.  
    9. For Each rel In MyDB.Relations
    10.   If rel.Table = sTableName Or rel.ForeignTable = sTableName Then
    11.     Debug.Print vbCrLf & "RELATION NAME: " & rel.Name & " FROM " & rel.Table & " TO " & rel.ForeignTable
    12.     Debug.Print "  |--" & IIf(rel.Attributes And dbRelationDontEnforce, "Don't Enforce", "ENFORCE")
    13.     Debug.Print "  |--" & IIf(rel.Attributes And dbRelationDeleteCascade, "DELETE Cascade", "No DELETE Cascade")
    14.     Debug.Print "  |--" & IIf(rel.Attributes And dbRelationUpdateCascade, "Update Cascade", "No Update Cascade")
    15.  
    16.     For Each fld In rel.Fields
    17.       Debug.Print "    |-- [" & fld.Name & "] ==> [" & fld.ForeignName & "]"
    18.     Next fld
    19.   End If
    20. Next rel
  2. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. RELATION NAME: EmployeesEmployee Privileges FROM Employees TO Employee Privileges
    2.   |--ENFORCE
    3.   |--DELETE Cascade
    4.   |--No Update Cascade
    5.     |-- [ID] ==> [Employee ID]
    6.  
    7. RELATION NAME: EmployeesOrders FROM Employees TO Orders
    8.   |--ENFORCE
    9.   |--No DELETE Cascade
    10.   |--Update Cascade
    11.     |-- [ID] ==> [Employee ID]
    12.  
    13. RELATION NAME: EmployeesPurchase Orders FROM Employees TO Purchase Orders
    14.   |--Don't Enforce
    15.   |--No DELETE Cascade
    16.   |--No Update Cascade
    17.     |-- [ID] ==> [Created By]
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,313
So, if a relationship is NOT "dbRelationDontEnforce", then it is "Enforced"? Which makes perfect, logical sense.

Is there any way we can know whether a Relationship is One-to-Many? Or, again, if the relationship is NOT "dbRelationUnique", then it is, by default, One-to-Many. This, again, makes perfect, logical sense.

Soooooo, going forward, if a relation attribute has a value of 0, then, it is:
  • One-to-Many (i.e., NOT Unique)
  • Enforced (i.e., NOT Do Not Enforce)
  • Resident Tables (i.e., NOT inherited from linked tables)
  • No Cascade Updates/Deletes
  • INNER JOIN (i.e., neither LEFT or RIGHT joined)
This all is logically implied by the other Attribute values that don't exist in the value 0, rather than being explicitly stated by the value itself.

Or, am I completely misunderstanding this? But, for my purposes, it will certainly suffice.

Thanks all, as usual!
2 Weeks Ago #4

ADezii
Expert 5K+
P: 8,668
I guess the logical approach would be to analyze each Bit of the Attributes Property:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rel As DAO.Relation
  3. Dim fld As DAO.Field
  4. Dim sTableName As String
  5.  
  6. Set MyDB = CurrentDb
  7. sTableName = "Employees"
  8.  
  9. For Each rel In MyDB.Relations
  10.   If rel.Table = sTableName Or rel.ForeignTable = sTableName Then
  11.     Debug.Print vbCrLf & "RELATION NAME: " & rel.Name & " FROM " & rel.Table & " TO " & rel.ForeignTable
  12.  
  13.     For Each fld In rel.Fields
  14.       Debug.Print "  |-- [" & fld.Name & "] ==> [" & fld.ForeignName & "]"
  15.     Next fld
  16.  
  17.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationUnique, "1 to 1", "1 to MANY")
  18.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationDontEnforce, "Don't Enforce", "ENFORCE")
  19.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationDeleteCascade, "DELETE Cascade", "No DELETE Cascade")
  20.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationUpdateCascade, "Update Cascade", "No Update Cascade")
  21.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationInherited, "Inherited", "NOT Inherited")
  22.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationLeft, "LEFT Join", "RIGHT Join")
  23.     Debug.Print "    |--" & IIf(rel.Attributes And dbRelationRight, "RIGHT Join", "LEFT Join")
  24.   End If
  25. Next rel
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,313
All this does make sense, ADezii!

I guess, because (normally) a 0 value for any type of attribute on anything else ever in the digital world indicates "nothing", for Access to have 0 indicate much more than that is confusing. But, now that I understand that the 0, in this case, indicates "the absence of exceptions", it makes much more sense. I'll just view the 0 attribute as the standard relationship attribute.

At least that terminology helps me grasp the concept better. This will drive me changing some of my coding for a project I am working on--but at least now, I have a concrete direction to move in, thanks to your code framework.

I appreciate all y'all's time.

Grace and peace!
2 Weeks Ago #6

ADezii
Expert 5K+
P: 8,668
Also, you don't have to rely on the Intrinsic Constants to test each Bit in Attributes. You can create your own Enumeration for this purpose.
2 Weeks Ago #7

Rabbit
Expert Mod 10K+
P: 12,374
In this case, the absence of a relationship would be a lack of an entry in Database.Relationships rather than an entry in Database.Relationships with .Attributes = 0.

Otherwise, Database.Relationships would be have to represent every table "related" to every other table. And a new table added to the database would require a ton of new entries into Database.Relationships.
2 Weeks Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.