Alias table in relationship

[Guest]

So little knowledge--So much help. I have to thank those who have answered
my questions. Now I have another. I read in Access 2003 Inside/Out or Step
by Step that an alias table can be created in the relationship window by
adding the same table twice. If I am remembering correctly the name of one
of the tables can be change to create a relationship to the original there by
providing a way to link and draw fields from the alias table.

I have an employee table, a family table, a payroll table, an attendance
table. I would like to create a relationship to the from the employee table
to the attendance table and/or the payroll table. I already have a
relationship between the employee table and the family table. This
relationship provided a way for me to create a subform from the family table
into the employee table. I would prefer another form for the attendance and
payroll table and would like to choose the maangers from the employee table.
Also, I would like to use a subform from the employee table for the
attendance and payroll forms. It appears to me that I have some real work to
do.

My question may be a simple one for someone if I am attempting to forms and
subforms. How do I change the name of the alias employee table in the
relationship window??

Am I missing something obvious.

 

[Allen Browne]

AFAIK, you can't change this name, at least graphically.

If the table appears more than once, Access adds an underscore and numeric
suffix. That's the alias.

I haven't tried to investiage it further, but the names are there in a
system table so it may be possible to modify them. If you are interested,
you might examine how Stephen Lebans created a utility to allow you do save
and restore multiple relationship views:
http://www.lebans.com/saverelationshipview.htm

 

[Allen Browne]

"Programming this info"?

What are you seeking to achieve?

 

[Guest]

All of the company's forms and tables were set up in Lotus Smart Suite
Approach. It has become necessary to restructure the information so that the
employees can work in MS Access; therefore, I have to transfer the
information from Approach to Access. In some cases such as the employee info
there will be changes. In approach there was no relationship window and the
tables were a bit different. Although for the most part I can import the
info with some restructuring.

Thanks again for your help.

 

[Allen Browne]

That's good. I don't follow why it's important to name your own aliases for
the relationship window though.

If you want to programmatically list the relationships that exist in an
Access database, this might help you pin down what is related to what and
how:

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

 

[Guest]

Sorry for the delay in answer. Big help. Thanks.