MS Access - Relationships

Question

I inherited a project in Microsoft Access 2000 that has code, tables & queries. I need to convert this to SQL.

I see several queries with INNER JOINS with Select commands. So I am guessing the tables have relations set up?

I used the Relationships tools (Graphical Tool) & add all the tables. The relationships are not showing up.

How has the relationships setup? How do I retrieve the relations that has been set?

HansUp · Accepted Answer · 2011-08-19T03:14:39.510

The database engine will accept JOIN statements regardless of whether or not relationships have been defined for the joined tables. So those INNER JOIN queries don't necessarily mean your database has any relationships defined.

Information about relationships is stored in the hidden system tables, MSysObjects and MSysRelationships. You can use the database's Relations collection to view that information.

Public Sub InspectRelations()
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    For Each rel In CurrentDb.Relations
        Debug.Print "Relationship Name: " & rel.name
        If rel.Attributes And dbRelationDontEnforce = dbRelationDontEnforce Then
            Debug.Print "Relationship not enforced"
        Else
            Debug.Print "Relationship enforced"
        End If
        Debug.Print "Table: " & rel.Table
        Debug.Print "ForeignTable: " & rel.ForeignTable
        For Each fld In rel.Fields
            Debug.Print "Field Name: " & fld.name
            Debug.Print "ForeignName: " & fld.ForeignName
        Next fld
        Debug.Print String(10, "-")
    Next rel
    Set fld = Nothing
    Set rel = Nothing
End Sub

Edit: You can examine Attributes of a relation. I included dbRelationDontEnforce in the example. Other attributes such as dbRelationDeleteCascade and dbRelationUpdateCascade might be useful. Look at the help topic for the RelationAttributeEnum Enumeration to see the full list of available attributes.

Edit2: To use the code, open your database in Access. Then use Ctrl+g shortcut to open the Immediate Window in the Visual Basic Editor. Then select Insert->Module from the VBE main menu. Copy the code and paste it into the module. Save the module, either accept the name (which will be like "Module1") Access offers, or give it a different name which is more meaningful to you ... but do not name the module InspectRelations --- the module and procedure should have different names.

Make sure the compiler accepts the code by choosing Debug->Compile from the main menu. Since you're using Access 2000, you may need to set a reference for "Microsoft DAO 3.6 Object Library". Find it in the list and place a check mark next to it, then click OK. Then compile again.

If it compiled without errors, you can run the procedure by locating the cursor within the body of the procedure and pressing F5 to run it. The Debug.Print statements send their output to the Immediate Window.

Although I am able to follow the code, I an sorry, I have never done any access programming. Where should I put this code in? How do I run it? Where will the output shou up? — user450143, Aug 19 '11 at 02:14

score 0 · Answer 2 · answered Aug 18 '11 at 09:08

0

If the Relationship tool was not used to indicate the relationships and enforce referential integrity, you are out of luck as you can have relationships between tables without using the Relationship tool.

The joins show you the primary keys/foreign keys of the involved tables, so you probably have to reverse engineer the relationships from the JOIN clauses in the queries.

answered Aug 18 '11 at 09:08

Jacob

41,721
6
79
81

So do I serach for all JOINs in the code & see how the relations are setup? Also when I used the Relationship Tool, bunch of queries showed up. Will they help me? – user450143 Aug 18 '11 at 09:14
Yes, but only CTRL+F for `JOIN` might not be enough as there could be implicit joins etc. – Jacob Aug 18 '11 at 09:15
Note: the CTRL-F will not find join's in your query objects. You will have to inspect each one. – BIBD Aug 18 '11 at 16:33

MS Access - Relationships

2 Answers2