Can't find relationship...

[Tanya Lee]

I am trying to delete a field in a table and it is telling me that I must
delete the relationships to that table/field first. I understand that, but
when I open the relationships window, it doesn't show any relationships. This
has never happened to me.

Any suggestions??
Thanks!

 

[Roger Carlson]

Have you clicked the Show All Relationships button?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tanya Lee]

Yes, I have click on the show all and the show direct relationships button.
It is showing no joins whatsoever.

 

[Roger Carlson]

That is strange. Have you tried Compacting and Repairing? Make a backup
first!

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Boyce]

Any chance your table includes one/more "lookup" type fields? I believe
these set their own relationships, which you may not see in the
relationships window.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

On Wed, 10 May 2006 06:00:02 -0700, Tanya Lee <Tanya

I am trying to delete a field in a table and it is telling me that I must
delete the relationships to that table/field first. I understand that, but
when I open the relationships window, it doesn't show any relationships. This
has never happened to me.

Any suggestions??
Thanks!

Sometimes the relationships window gets messed up. One idea would be
to use this VBA routine, or an edited version of it - this one is a
small tactical nuclear device which removes ALL relationships between
ALL tables in your database:

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



John W. Vinson[MVP]

 

[Tanya Lee]

I will give that a try...I'm at a loss. Thank you.

 

[Tanya Lee]

Yes it does, but I have about 13 tables that are similar to this one, and
this is the only one giving me a problem, so could be some kinda glitch - I
think my best bet may be to recreate that specific table from scratch before
I start entering data.
Thanks.

 

[Jeff Boyce]

Tanya

If any of your tables have "lookup" data type fields, Access will be
creating hidden indexes & relationships. Moreover, having the table display
one thing but store something else (the foreign key) means you'll have to
take extra care with forms, queries, etc. to be sure you're using the
foreign key value and not what you SEE in the table.

A scan through the tablesdbdesign newsgroup will show a strong consensus
advising against using lookup data type fields.

Best of luck on your project!

Jeff Boyce
Microsoft Office/Access MVP