Relationships in Query Design View not the same as Relationship window

[Lucentus]

Hello, all.

I am using Access 2000 on Windows 2000 and NT, and have about 46 linked tables from a MySQL database. My problem is as follows.

I have a table, "Employer", with a single integer key, "Emp_ID". I have a bunch of tables containing the information for employer contacts, named "Contact", "Contact_Address", "Contact_Email", etc. Each of the Contact tables contains an Emp_ID as well as a Contact_ID to keep track of individual contacts. In this fashion, an Employer can have any number of contacts.

On the Relationships "screen", in Access, Employer is linked to Contact where all records from Employer are taken and only those with matching join types from Contact are taken. (This is join type 2, I believe). They are related on the Emp_ID field (Employer.Emp_ID -> Contact.Emp_ID).

Contact has the same type of relationship to all of the other Contact tables, except it's related on Emp_ID as well as Contact_ID (Contact.Emp_ID -> Contact_Email.Emp_ID, Contact.Contact_ID -> Contact_Email.Contact_ID etc).

Everything is fine with that, except that when I go to run queries, Access adds new relational links between the Employer and extended Contact tables that I did not specify in Relationships, thus breaking my queries. Is there a way to prevent that?

None of this would be a problem if I were doing this, myself; I have no problem moving my mouse over and deleting the link. The issue is that my client has employees with varying levels of Access aptitude, and they have expressed concern at having to interact too much with the Query Design tool. Unfortunately, they need specific attributes in their queries that prevent them from simply using the handy wizard.

Help would be appreciated.

Regards,
Eric B Venet
MagusNoctis@yahoo.com

 

[jobo123]

Hi there,

In relationships have you tried the 'Show All Relationships' button. You may find that the unwanted relationships appearing in your queries are revealed by this and can then be deleted.

Hope this helps, Jobo123
www.dbLetterWriter.com

 

[danvlas]

Tools-Options-Tables/Queries tab
Uncheck the Enable AutoJoin box

I have a feeling that the extra joins are made on fields that have the same name...

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[wilsonaa]

Sometimes Show all relationships does not infact show all relationships. Try this code to document your code to a text file.

Sub MapDatabase()
Dim dbs As Database, tdf As TableDef, fld As Field
Dim idx As index, rel As Relation
Dim intX As Integer
Dim Filename As String
Dim varStatus As Variant

Filename = Application.CurrentProject.Path & "\" & "TMSDoc.txt"
Open Filename For Output As #2

Set dbs = CurrentDb

Database:

' Map the Database properties.

Print #2, "DATABASE"
Print #2, "Name: ", dbs.Name
Print #2, "Connect string: ", dbs.Connect
Print #2, "Transactions supported?: ", dbs.Transactions
Print #2, "Updatable?: ", dbs.Updatable
Print #2, "Sort order: ", dbs.CollatingOrder
Print #2, "Query time-out: ", dbs.QueryTimeout

TableDef:

' Map the TableDef objects.
Print #2, "TABLEDEFS"
For Each tdf In dbs.TableDefs
varStatus = SysCmd(acSysCmdSetStatus, "Processing Table " & tdf.Name)
Print #2, "Name: ", tdf.Name
Print #2, "Name: ", tdf.DateCreated,
Print #2, "Name: ", tdf.LastUpdated,
If tdf.Updatable = True Then
Print #2, "Updatable",
Else
Print #2, "Not Updatable",
End If

' Show the TableDef Attributes.
Print #2, Hex$(tdf.Attributes)
If (tdf.Attributes And dbSystemObject) <> 0 Then
Print #2, &quot;System object&quot;
End If
If (tdf.Attributes And dbAttachedTable) <> 0 Then
Print #2, &quot;Linked table&quot;
End If
If (tdf.Attributes And dbAttachedODBC) <> 0 Then
Print #2, &quot;Linked ODBC table&quot;
End If
If (tdf.Attributes And dbAttachExclusive) <> 0 Then
Print #2, &quot;Linked table opened in exclusive mode&quot;
End If

' Map Fields for each TableDef object.
Print #2, &quot;FIELDS&quot;
For Each fld In tdf.Fields
Print #2, &quot;Name: &quot;, fld.Name
Print #2, &quot;Type: &quot;, fld.Type
Print #2, &quot;Size: &quot;, fld.Size
Print #2, &quot;Attribute Bits: &quot;, Hex$(fld.Attributes)
Print #2, &quot;Collating Order: &quot;, fld.CollatingOrder
Print #2, &quot;Ordinal Position: &quot;, fld.OrdinalPosition
Print #2, &quot;Source Field: &quot;, fld.SourceField
Print #2, &quot;Source Table: &quot;, fld.SourceTable

' Show the Field Attributes here.
Print #2, Hex$(fld.Attributes)
If (fld.Attributes And dbSystemObject) <> 0 Then
Print #2, &quot;System Object&quot;
End If
Next fld ' Get the next Field in the TableDef object.

Indexes:

' Map Indexes for each TableDef object.
Print #2, &quot;INDEXES&quot;
For Each idx In tdf.Indexes
' Set the Index variable.
Set idx = tdf.Indexes(intX)
varStatus = SysCmd(acSysCmdSetStatus, &quot;Processing Index &quot; & idx.Name)
Print #2, &quot;Name: &quot;, idx.Name
Print #2, &quot;Clustered: &quot;, idx.Clustered
Print #2, &quot;Foreign: &quot;, idx.Foreign
Print #2, &quot;IgnoreNulls: &quot;, idx.IgnoreNulls
Print #2, &quot;Primary: &quot;, idx.Primary
Print #2, &quot;Unique: &quot;, idx.Unique
Print #2, &quot;Required: &quot;, idx.Required
' Map the Fields of the Index.
For Each fld In idx.Fields
Print #2, &quot;Name: &quot;, fld.Name
Next fld ' Get the next Field in the Index.
Next idx ' Get the next Index in the TableDef object.
Next tdf ' Get next TableDef in the Database.

Relationships:

' Map the Relation objects.
Print #2, &quot;RELATIONS&quot;
For Each rel In dbs.Relations
varStatus = SysCmd(acSysCmdSetStatus, &quot;Processing Relationship &quot; & rel.Name)
Print #2, &quot;Name: &quot;, rel.Name
Print #2, &quot;Attributes: &quot;, rel.Attributes
Print #2, &quot;Table: &quot;, rel.Table
Print #2, &quot;ForeignTable: &quot;, rel.ForeignTable
' Map the Fields of the Relation objects.
For Each fld In rel.Fields
Print #2, &quot;Name: &quot;, fld.Name
Print #2, &quot;ForeignName: &quot;, fld.ForeignName
Next fld ' Get the next Field in the Relation object.
Next rel ' Get next Relation object in the Database.

Close #2
varStatus = SysCmd(acSysCmdClearStatus)
End Sub