List Table Relationships

[gray]

Hi

Access2002/2007

Any idea how I can programmatically read the relationships for a given table? That is to say read the names of the tables it is linked to and whether it is the one or the many in its relationships?

I've seen plenty on how to create and delete them e.g.
Currentdb.CreateRelation
blah blah
CurrentDb.Relations.Append

but can't see how to simply read existing ones?

Thanks

 

[RainLover]

There is the relationship window that shows you what you want.

 

[jdraw]

There are code examples at http://allenbrowne.com/func-dao.html

 

[gray]

Hi

Thanks for the reply. I'd read Allen's tips but couldn't see what I was after. I did find this on the Bytes forum however:-

 Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
MsgBox "Table Relationship= " & rel.Name & " Tbl=" & rel.Table & " FTbl=" & rel.ForeignTable
For Each fld In rel.Fields
MsgBox "Field Relationship Key=" & fld.Name & " FKey=" & fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing

Thanks

 

[RainLover]

That looks good.

Instead of a Message Box one could write to a Temp Table.

I wonder what it does with Composite Keys. Could be interesting.

I shall put this one aside for a little play tomorrow.

Thanks for the initial question, and for posting your results back.

Will be good to compare with the Relationship window.

PS

Assuming this works what are you going to do with the result.

 

[gray]

Hiya

I hoping to populate a treeview control.... so for example... list all from customers table... check for 'links' to other tables... such as Orders or Employees then list records within those...
I'm hoping that using the relationships will cut down the amount of hard-coding of links between the tables when populating the treeview... well that's the plan....

The downside of the treeview I'm told is that it is not native Access.. this means extra references in the VB editor... and the dll's for those references might not be on the m/cs hosting the FE... in true Microsoft style, I'll worry about that later..

cheers

 

[gemma-the-husky]

it isn't particularly hard to so this, but it is a bit painstaking to find the information you need.

here is a snippet you may find useful. When you create a relation access automatically creates an (hidden) index for the (FK) fields on the many side of the relation - with an index attribute of "foreign"

 

[RainLover]

Actually the information you want is in the sys Tables.

But the code you wrote may be better for what you want.

I tested it in 2003.

What I haven't tested as yet is Composite Keys. I think you will only get one Field back not two or how many there is.

As I said I am still to test that.

 

[gemma-the-husky]

rain

I have never used the sys tables for any of this. i use the tabledefs, indexes, and relations collection

for any keys, you need to iterate the indexes collection. each index comprises a sub-collection of fields. so a single key index only has 1 field in the collection. a composite key has mutliple fields.

iterate the properties of the index collection. it's far easier than trying to puzzle it out from the msys table, assuming the data is even avaialble in the msys tables

 

[RainLover]

Dave

The Data is there. I looked.

But as I said the code would be better to use in this case.

I am still to check out Composite Keys to see what result we get.

 

[gemma-the-husky]

my last post didn't post. so i'll try again

I never use the msys tables, and i am not sure if this stuff is all in those tables

i do this by iterating tabledefs, indexes and realtions collection

so an index includes a collection of fields. a single-field index only has 1 field in that collection. a composite field index has more than one.

you will probably get on better interating the properties collection of an index, than trying to find this stuff in an msys table - even if it is actually avaialble in there.

 

[RainLover]

Dave

Wires are getting crossed somehow.

Your post did show up, and then again. I answered in basic agreement that code would be better rather than use the sys Tables. Although the Tables do hold the required info.

But I still want to test the code against a Composite P Key. Best to be able to say with certainty after testing. Right?

 

[RainLover]

Just tested with Composite Keys.

Only did one quick test but it does work.

It lists both keys Separately with their Foreign Key.

Interesting how this code does not use the term Primary. Instead it uses Relationship Key.

This code does not recognize a Composite Index as a Key.

 

[RainLover]

Thats Pat.

Your warning Re MSys tables is duly noted.

I will have a play with your code.

Have you posted this in the Archives?

 

[gray]

Hi All

I'm most interested to read all this... if gents of your calibre have done/are doing this then I must be going in the right direction...

BTW in terms of Msys tables, rather then a name string test, I use an attribute check...

For Each Tbl_Obj In Db.TableDefs
If Tbl_Obj.Attributes = 0 Then
'It's not a system table

I'd like to credit who told me this but I can't remember for the life of me...

Thanks for all the help...especially about the tips on composite keys... I never thought of that so you may have saved me more headaches.... thanks

 

[RainLover]

There could be something wrong with my Comments in Post #13 re naming of Keys.

I need to get back to that.

I would also advise you to look into Pat Hartman's code. Another thing I need to do. She is usually spot on with her code and doesn't tend to overly complicate things.

Have you done a proper search on a Tree View. There must be something good out there. I have one but I don't know if it can be applied to what you need. I did not write it.

 

[gray]

Hi

I did find one or two good examples but they were a little over-engineered for my needs. But I'm gradually getting my head around TreeViews and, actually, they are not too difficult, if a little labourious.... nothing new there!

Thanks again for the input.