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
