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

Phantom Relationships

P: n/a
Max
Hi. I really hope someone can help me. Going slowly insane with this
problem.

I have a two Access 2000 databases. One is the backend containing
tables and some admin queries. The other is the front end with forms /
queries and links to the tables in the back end.

From the Relationships window I selected File / Print Relationships.
The resulting report shows relationships that are not displayed in the
relationships window. Some of these relationships are plain wrong –
linking two tables on fields that are not actually related. I have…
clicked the "show all", "show direct" buttons
removed and added the tables in the relationship window a dozen times
created the "wrong" relationships again and deleted them
deleted all relationships
compacted and repaired a dozen times.

And still, these wrong relationships show up in the report, and no
relationships show in the relationship window.

What seems to be related is that I found I had some strange indexes in
several tables – the index name being the same as a field name, but
composed of multiple fields. I don't know where these came from but
they were the same fields as are "wrongly" linked. So, I have now
removed all indexing from all my tables.

But checking in the MSysRelationships table there are still 25 records
of defined relationships, none of which I can see in the relationships
window and some of which are duplicated.

This backend does contain some queries for updating and the like.
However, non of them involve creating a link between the tables in
question.

Has anyone got any suggestions as to what could be causing this? I
can't find anything on the KB and a couple of similar posts here
didn't come up with any answers.

Any suggestions would be much appreciated.

Thanks,
Max
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Max

Whenever you create a relationship with enforced Referential Integrity,
Access creates a hidden index to manage it. The relationship will be named
after the tables/fields if the name is available; otherwise it will use a
GUID.

Additionally, Access automatically creates indexes on any fields that have a
name ending with ID, Code, Num, etc. unless you remove the entries under:
Tools | Options | Tables/Queries | AutoIndex

To add to the confusion, the Name AutoCorrect feature tries to keep track of
changes to the table names or field names. This feature creates *many*
problems, with well over a dozen known bugs listed in:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

There may also be bugs in the relationship report, but I have not
experienced those.

Try this process to rebuild your database:

1. Delete all relationships
The code in this link will delete all the relations in your database, even
the hidden ones:
http://members.iinet.net.au/~allenbrowne/DelRel.html
Then compact the database.

2. Create a new (blank) database.

3. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General.

4. Import everything from the damaged database:
File | Get External | Import

5. Create the relationships from scratch.

Finally, Stephen Lebans has a utility that reads the details of the
relationships as Access has them stored, and lets you save and restore
multiple relationship window layouts. His code may give you clues as to how
Access is storing this data if you want to investigate the problem further.
See:
http://www.lebans.com/saverelationshipview.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Max" <mf*******@hotmail.com> wrote in message
news:24************************@posting.google.com ...
Hi. I really hope someone can help me. Going slowly insane with this
problem.

I have a two Access 2000 databases. One is the backend containing
tables and some admin queries. The other is the front end with forms /
queries and links to the tables in the back end.

From the Relationships window I selected File / Print Relationships.
The resulting report shows relationships that are not displayed in the
relationships window. Some of these relationships are plain wrong -
linking two tables on fields that are not actually related. I have.
clicked the "show all", "show direct" buttons
removed and added the tables in the relationship window a dozen times
created the "wrong" relationships again and deleted them
deleted all relationships
compacted and repaired a dozen times.

And still, these wrong relationships show up in the report, and no
relationships show in the relationship window.

What seems to be related is that I found I had some strange indexes in
several tables - the index name being the same as a field name, but
composed of multiple fields. I don't know where these came from but
they were the same fields as are "wrongly" linked. So, I have now
removed all indexing from all my tables.

But checking in the MSysRelationships table there are still 25 records
of defined relationships, none of which I can see in the relationships
window and some of which are duplicated.

This backend does contain some queries for updating and the like.
However, non of them involve creating a link between the tables in
question.

Has anyone got any suggestions as to what could be causing this? I
can't find anything on the KB and a couple of similar posts here
didn't come up with any answers.

Any suggestions would be much appreciated.

Thanks,
Max

Nov 13 '05 #2

P: n/a
Max
Fantastic! Thank you Allen

That seems to have done the trick.

I think I had incorrectly created a relationship in the past - causing
Access to create these hidden indexes. I realised my mistake and
deleted the relationship but the indexes remained and somehow casued
the relationships to persist.

Story of my life, appropriately enough.

Anyway, I think it's ok now.

Thanks again for your help.

Max

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40**********************@per-qv1-newsreader-01.iinet.net.au>...
Hi Max

Whenever you create a relationship with enforced Referential Integrity,
Access creates a hidden index to manage it. The relationship will be named
after the tables/fields if the name is available; otherwise it will use a
GUID.

Additionally, Access automatically creates indexes on any fields that have a
name ending with ID, Code, Num, etc. unless you remove the entries under:
Tools | Options | Tables/Queries | AutoIndex

To add to the confusion, the Name AutoCorrect feature tries to keep track of
changes to the table names or field names. This feature creates *many*
problems, with well over a dozen known bugs listed in:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

There may also be bugs in the relationship report, but I have not
experienced those.

Try this process to rebuild your database:

1. Delete all relationships
The code in this link will delete all the relations in your database, even
the hidden ones:
http://members.iinet.net.au/~allenbrowne/DelRel.html
Then compact the database.

2. Create a new (blank) database.

3. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General.

4. Import everything from the damaged database:
File | Get External | Import

5. Create the relationships from scratch.

Finally, Stephen Lebans has a utility that reads the details of the
relationships as Access has them stored, and lets you save and restore
multiple relationship window layouts. His code may give you clues as to how
Access is storing this data if you want to investigate the problem further.
See:
http://www.lebans.com/saverelationshipview.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Max" <mf*******@hotmail.com> wrote in message
news:24************************@posting.google.com ...
Hi. I really hope someone can help me. Going slowly insane with this
problem.

I have a two Access 2000 databases. One is the backend containing
tables and some admin queries. The other is the front end with forms /
queries and links to the tables in the back end.

From the Relationships window I selected File / Print Relationships.
The resulting report shows relationships that are not displayed in the
relationships window. Some of these relationships are plain wrong -
linking two tables on fields that are not actually related. I have.
clicked the "show all", "show direct" buttons
removed and added the tables in the relationship window a dozen times
created the "wrong" relationships again and deleted them
deleted all relationships
compacted and repaired a dozen times.

And still, these wrong relationships show up in the report, and no
relationships show in the relationship window.

What seems to be related is that I found I had some strange indexes in
several tables - the index name being the same as a field name, but
composed of multiple fields. I don't know where these came from but
they were the same fields as are "wrongly" linked. So, I have now
removed all indexing from all my tables.

But checking in the MSysRelationships table there are still 25 records
of defined relationships, none of which I can see in the relationships
window and some of which are duplicated.

This backend does contain some queries for updating and the like.
However, non of them involve creating a link between the tables in
question.

Has anyone got any suggestions as to what could be causing this? I
can't find anything on the KB and a couple of similar posts here
didn't come up with any answers.

Any suggestions would be much appreciated.

Thanks,
Max

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.