Answers inline.
--
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.
"eskil" <es********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
I want to link products and contacts, products and axctivities and
activities and contacts. I am using one to many links with full
referential integrity.
That all sounds very good. No problem. It really helps reduce problems with
your database if you do everything you can at the engine-level:
relationships with RI, field-level validation, table-level validation,
setting AllowZeroLength to No, removing the zero Default Value and setting
Required for foreign key fields, and so on.
Each table is linked to a table that defines the
possible relationship between either 2 tables.
Not sure I understand this part of your question. Are you restating the same
as the previous paragraph? Or do you mean that you have created your own
table to store information about the relationships the table has? No need to
do that, as Access already has such as table (MSysRelationships), and you
can loop through the Relations collection quite easily. The function below
demonstates that.
My question is: does this increase the risk for data corruption or pose
other problems that need be taken into account before plunging in?
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
