Relationships not visible

[Guest]

Hi,

I know that the first answer to the questions of 'why can't I see my
relationships?' is to go into the relationships screen and click on show
all.... It doen't show me anything. Everything dissapeared some time ago and
I thought that the database designer had done something but appently they
just dissapeard, but as everything carried on working... you now 'if it's not
broke'.

I am having various problems and after trying a number of options I want to
create a new backend but as this involves recreating the relationships - I
need to be able to see them to get this right.

Any help would be greatly appreciated and thanks in advance

Charlie

 

[Allen Browne]

It might help to know if the relations are present and hidden, or if they
have actually been lost.

The function below should help you identify that.
Paste it into a module (code window.)
Press Ctrl+G to open the Immediate window.
Enter:
? ShowRel()
and see if you get a list of relations, showing what tables are linked, with
a 2nd line for each one identifying the fields.

Public Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

 

[Guest]

Thanks for getting back to me however I am getting a compile error 'Invalid
outside procedure' on Print ShowRel()

My knowledge od VB is limited and I am nor sure of where to go next?

Charlie

 

[Allen Browne]

The message indicates that it is not pasted in the right place. Remove it,
and try the following.

1. On the Modules tab of the Database window, click New
Access opens a code window.

2. Paste the code in there.
Save it with a name such as Module1.

3. This shows as 2 lines in the posting, but is just one long line:
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)

4. Check that Access understands it:
Choose Compile on the Debug menu.
You will need to fix any problems, and repeat until there are none.

If you are using Access 2002 or 2000, you may need to add a reference to the
DAO library. Choose References on the Tools menu (in the code window). Check
the box beside:
Microsoft DAO 3.6
More info about references:
http://allenbrowne.com/ser-38.html

 

[Guest]

Hi Allen,

I'm sorry if this sounds really basic but I don't know how to fix the
compile error Invalid outside procedure. The module automatically changes
the line ? ShowRel() to PRINT ShowRel() should it do this and is that what
is causing the compile error?

Charlie

 

[Allen Browne]

The ? does mean print, so the fact that VBA changes is not a problem.
However, it would normally change it only if you do that in the VBA code.

The idea was to leave the module exactly as it was, then open the Immediate
Window (Ctrl+G), and do the ? ShowRel() bit there (not in the original
code.)

If you still can't get that to work, you could create a command button, and
set its On Click property to:
= ShowRel()

 

[Guest]

K it did nothing I put the code into a new module saved this. Entered ?
ShowRel() into the immediate window and nothing. I created a new relationship
which does show when this is executed

If the relationships are missing, how is the database functioning properly?
all tables that are linked via the relationships are upto date and hold the
correct information.

I will need to create a new and clean backend before the next stage of
development which is immenent. Is there anything I can do in respect of the
missing relationships?

Charlie

 

[Allen Browne]

You can recreate them.

But it is a little difficult to track what is happening, when we don't even
know whether the relations are there or not.

In the Immediate Window, you could try:
? Currentdb.Relations.Count
Won't give you the list, but it may give you a clue.

 

[John Vinson]

You can recreate them.

But it is a little difficult to track what is happening, when we don't even
know whether the relations are there or not.

In the Immediate Window, you could try:
? Currentdb.Relations.Count
Won't give you the list, but it may give you a clue.

here's a routine I've had around for a long time:

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub

John W. Vinson[MVP]

 

[Guest]

Thank you both for your assistance,

I have followed both instructions and my relationship count is 0..... I
don't understand how everything is still working when there is nothing there.
I have spoken to the developer who thinks that they may have dissapeared
around the time that access was upgraded fron office 2000 to office xp
whereas now we are on 2003 - could the upgrade have anything to do with it?

Any ideas where I go to from here?

Charlie

 

[Allen Browne]

The database can "work" without the relationships.
But, of course, you can end up with bad data, orphaned records, and so on.

It will therefore be a matter of removing or correcting the bad data (use
the Unmatched Query Wizard), and then creating the correct relationships.
The develper can probably send you a blank copy of the database, or at least
a graphic of the relationships diagram so you can go to Tools |
Relationships and create the relationships again.

 

[aaron.kempf]

relationships in MDB are a joke.
I mean.. you can't use RI against multiple tables because you need each
table in a seperate db lol

Access Data Projects allow REAL RI and it also allows constraints and
triggers.
Additionally; you can have 100 different database diagrams; instead of
being stuck with a half-ass implementation called 'relationships'

MDB is for babies.

Access Data Projects are bigger, better, faster for end users and
easier to develop with.

-Aaron
ADP Nationalist