Re: duplicate tables in ShowAll Relationships?
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sat, 13 Jan 2007 09:55:02 +0900
If you want to bypass the Relationships window, and examine the relations programmatically, that might give some extra clues as to what is going on. Particularly, look for any relations.
Just a thought, but it might be that the Name AutoCorrect feature has Access confused about the name of something, and hence the duplicated relations or misguided aliasing. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
and then compact the database:
Tools | Database Utilities | Compact/Repair
Details of some of the problems with Name AutoCorrupt:
http://allenbrowne.com/bug-03.html
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
--
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.
"LF" <LF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8186E8E0-0810-4287-BC95-D157B41CC709@xxxxxxxxxxxxxxxx
That makes sense to me if the foreign keys are not the same in each duplicate
table; or even if the foreign keys were the same and pointed to different
primary keys in the same table. In my instance the foreign keys are the same
in each duplicate table and both point to the same primary key in the same
table.
--
LF
"Allen Browne" wrote:
That should occur if you have more than one join between any pair of tables.
For example, in a shipping application, you might have a table of Clients,
and a table for shipped products. The Shipped table might have a Consignor
field (who sent it) and a Consignee field (who it was sent to.) Both these
fields are foreign keys to the Client table. Since there are 2 relatitions
between Shipped and Client, Access displays the 2nd one by showing a 2nd
copy of the Clients table, and aliasing it as Clients_1.
If that does not describe what you are seeing, you may have duplicate
relationships, or possibly a corruption.
"LF" <LF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D4610706-E6BD-439C-9D1D-786838991840@xxxxxxxxxxxxxxxx
> I'm curious, why when I select the ShowAll command within the
> Tools>Relationships window does it display tables twice in several
> instances
> where _1 is appended to one of the names?
> -- > LF
.
- References:
- Re: duplicate tables in ShowAll Relationships?
- From: Allen Browne
- Re: duplicate tables in ShowAll Relationships?
- Prev by Date: Getting from Acces to word (CONT)
- Next by Date: Re: Require related record
- Previous by thread: Re: duplicate tables in ShowAll Relationships?
- Next by thread: Re: Table Validation
- Index(es):
Relevant Pages
|
