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.

 

[Pat Hartman]

Here's a procedure I use from my documentation database. It analyzes tables from OTHER databases so you'll see that the code uses ThisDB for the code database and db for the database containing the tables to be analyzed. It builds a table with the relationship information.

Microsoft specifically says to not use the MSys tables for stuff like this because they can't guarantee that the schema will remain the same from version to version and having already been burned by changes to MSys tables, I'm inclined to go along.

Sub Create_tblTableIndexes()

    Dim db As DAO.Database
    Dim ThisDB As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim idxLoop As DAO.Index
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim Position As Integer
    Dim CountIndexes As Integer
    Dim strDatabase As String

    'strDatabase = "C:\hartman\ImportDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountIndexes = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    Set QD1 = ThisDB.QueryDefs!QdeltblTableIndexes
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblTableIndexes
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
             
'' add error checking for 3024 - not found in collection
'' add error checking for 3110 - no permission to read msysmodules2

        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            CountIndexes = CountIndexes + 1
            Forms!frmPrintDoc!txtIndexCount = CountIndexes
            Forms!frmPrintDoc!txtIndexName = tblLoop.Name
            Forms!frmPrintDoc.Repaint
        
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                Position = 1
                For Each fldLoop In idxLoop.Fields
                    TempSet1.AddNew
                        TempSet1!IndexName = idxLoop.Name
                        TempSet1!Unique = idxLoop.Unique
                        TempSet1!OrdinalPosition = Position
                        TempSet1!FieldName = fldLoop.Name
                    TempSet1.Update
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

    db.Close
Exit Sub

ErrorHandler:

Select Case Err.Number
    Case 3110
        MsgBox "Open " & strDatabase & " and change the admin security to allow read for MSysModules", vbOKOnly
    Case 3043
        MsgBox "Please select a valid database", vbOKOnly
    Case 91   ' db was not opened so it cannot be closed.
        Exit Sub
    Case Else
        MsgBox Err.Number & "-" & Err.Description
End Select
End Sub

 

[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.