Susan, let's just verify that these are not *attached* tables?
If they are local tables, it would appear that there is a corruption in the
database. Typically this is caused by Name AutoCorrect confusing Access
about the names of the tables and fields, though there are other causes
(such as an out-of-date copy of msjet40.dll.)
Here's some basic steps to rebuild the database. After this your relations
should be fine.
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
4. Open Access (holding down the Shift key if you have any startup code),
and compact again.
5. Create a new (blank) database.
6. Before doing anything else, turn off the Name AutoCorrect boxes (as in #1
above.)
7. Import everything *without* the relations:
File | Get External | Import
Before clicking Ok, click the Options button.
This expands the dialog.
Uncheck the Relationships button.
Then select all your tables, and import them.
8. Rebuild all the relations.
9. After verifying the data and relations are okay, Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html
10. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.
If you would prefer to examine the relations, the code below may help you do
that. Otherwise ignore the rest of this message.
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
Private Function RelationAttributes(lngAttrib As Long) As String
Dim strOut As String
Dim lngLen As Long
Const dbRelationCascadeNull As Long = &H2000
If (lngAttrib And dbRelationUnique) <> 0& Then
strOut = strOut & "unique, "
End If
If (lngAttrib And dbRelationDontEnforce) <> 0& Then
strOut = strOut & "unenforced, "
End If
If (lngAttrib And dbRelationInherited) <> 0& Then
strOut = strOut & "inherited, "
End If
If (lngAttrib And dbRelationUpdateCascade) <> 0& Then
strOut = strOut & "cascade update, "
End If
If (lngAttrib And dbRelationDeleteCascade) <> 0& Then
strOut = strOut & "cascade delete, "
End If
If (lngAttrib And dbRelationCascadeNull) <> 0& Then
strOut = strOut & "cascade to null, "
End If
If (lngAttrib And dbRelationLeft) <> 0& Then
strOut = strOut & "left join, "
End If
If (lngAttrib And dbRelationRight) <> 0& Then
strOut = strOut & "right join, "
End If
lngLen = Len(strOut) - 2& 'Without trailing comma and space.
If lngLen > 0& Then
RelationAttributes = Left$(strOut, lngLen)
End If
End Function
Function HasDeleteCascade(strTable As String) As Boolean
Dim db As DAO.Database
Dim rel As DAO.Relation
Set db = CurrentDb()
For Each rel In db.Relations
If rel.Table = strTable Then
If (rel.Attributes And dbRelationDeleteCascade) > 0 Then
Debug.Print rel.Name, rel.ForeignTable
HasDeleteCascade = True
Exit For
End If
End If
Next
Set rel = Nothing
Set db = Nothing
End Function
And if you need to programmatically delete the relations, see:
http://allenbrowne.com/DelRel.html