Relationships in access 2004

[Mike1041]

I have 4 fields in a table that were were created linked to another table
using Look-up.
I tried to delete these fields from the table and ge the message that I
cannot delete since they are part of one or more relationships.
I checking the relationships, I cannot find any for these fields. Somehow
there is a "hidden" relationship that I cannot find.
Any suggestions would be most appreciated.

 

[Tom van Stiphout]

On Mon, 8 Sep 2008 19:47:08 -0700, Mike1041

(I'll skip over the A2004 typo)
Open the relationships window. Find the table or <shudder> put it on
there for the first time. Click on the toolbar button Show All
Relations.
There also is a programmatic way, if you don't mind writing some VBA
code. See the Relationships collection in the help file.

-Tom.
Microsoft Access MVP

 

[Mike1041]

I have already tried that. I cannon find any relationship to any table or
query.

 

[Tom van Stiphout]

On Mon, 8 Sep 2008 21:13:01 -0700, Mike1041

Maybe you have some kind of corruption. Export all your tables to a
new database.

-Tom.

 

[John W. Vinson]

I have 4 fields in a table that were were created linked to another table
using Look-up.
I tried to delete these fields from the table and ge the message that I
cannot delete since they are part of one or more relationships.
I checking the relationships, I cannot find any for these fields. Somehow
there is a "hidden" relationship that I cannot find.
Any suggestions would be most appreciated.

Lookup Fields will create invisible relationships. Try changing the lookup
fields from Combo Box to Textbox and then showing all relationships; or use
the following code (adapted as necessary):

Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = DBEngine(0)(0)
For inti = db.Relations.Count - 1 To 0 Step -1
Set rel = db.Relations(inti)
Debug.Print "Deleting relation "; rel.Name, rel.Table, rel.ForeignTable
db.Relations.Delete rel.Name
Next inti
End Sub


This is a small nuclear device - it will do as its name suggests, kill ALL
relations. You can use an If statement to select only those relationships that
have your table as rel.ForeignTable (the lookup tables will be Table).

 

[david]

Show system and hidden tables. Look in table MSysRelationships.
Do you see the relationship there?

(david)