Relationships - Internal or External?

[Tom Ventouris]

I have always understood that in a split application, the relationships
should be created in the BE.
I have inherited an application and would like to check this, along with
evrything else in the design, before I re-deploy.
Is there any way to check, other than clicking on every relationship in the
FE?

While on the subject, what are the effects or consequences of relationships
in the FE?

Thanks again.

 

[John W. Vinson]

I have always understood that in a split application, the relationships
should be created in the BE.
I have inherited an application and would like to check this, along with
evrything else in the design, before I re-deploy.

Well... just open the backend, open the relationships window, and show all
relationships.

Is there any way to check, other than clicking on every relationship in the
FE?

While on the subject, what are the effects or consequences of relationships
in the FE?

Almost purely decorative. If you create a Query involving two linked tables,
and a frontend relationship has been defined, it will default to that kind of
relationship; but referential integrity (the main reason for relationships!)
will not be enforced.

 

[Tom Ventouris]

Thank you.
What I am looking for are any relationships created in the FE, which will
not appear in the BE

 

[John W. Vinson]

Thank you.
What I am looking for are any relationships created in the FE, which will
not appear in the BE

Hrm. I'm not sure how you could, other than opening the frontend and showing
all relationships, and then doing the same for the backend and comparing! The
frontend inherits any relationships which exist in the backend, and I don't
know how one would distinguish an inherited relationship from a locally
created one.

You might be able to use, or adapt, this code on both databases and compare
the results:

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable, _
Hex(rel.Attributes)
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub

 

[Tom Ventouris]

I think that an inherited relationship in the FE displays a "(External)"
next to the Relationship Type. The Relationship Type is also greyd
out....unless I am reading this all wrong.
I can can, I I have this expalnation right, check whether a relattionship is
created in the FE or BE by opening each relationship in the FE and checking
the type. I was just wondering if there is a quicker way to do this in an
application with many relationships.

A direct comparirion between the BE and FE relationship windows is an
option. Thanks.

 

[Ken Sheridan]

You are quite correct. To separate the seep from the goats try this:

Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb

Debug.Print "Inherited relationships:"
For Each rel In CurrentDb.Relations
If rel.Attributes And dbRelationInherited Then
Debug.Print rel.Name
End If
Next rel

Debug.Print "Local relationships:"
For Each rel In CurrentDb.Relations
If Not rel.Attributes And dbRelationInherited Then
Debug.Print rel.Name
End If
Next rel

Unless you have an extremely large number of relationships I think the list
will be accommodated in the debug window. If not you could amend the code to
insert the name and relationship type into columns in an empty table.

Ken Sheridan
Stafford, England

I think that an inherited relationship in the FE displays a "(External)"
next to the Relationship Type. The Relationship Type is also greyd
out....unless I am reading this all wrong.
I can can, I I have this expalnation right, check whether a relattionship is
created in the FE or BE by opening each relationship in the FE and checking
the type. I was just wondering if there is a quicker way to do this in an
application with many relationships.

A direct comparirion between the BE and FE relationship windows is an
option. Thanks.

 

[aaron_kempf]

if you upsized the database to SQL Server; you wouldn't have to deal
with this mess.

Access can't even enforce relationships between databases.

-Aaron

 

[Tom Ventouris]

Thanks Ken. All done.
There were no relationsgips in the FE, but had to check.

You are quite correct. To separate the seep from the goats try this:

Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb

Debug.Print "Inherited relationships:"
For Each rel In CurrentDb.Relations
If rel.Attributes And dbRelationInherited Then
Debug.Print rel.Name
End If
Next rel

Debug.Print "Local relationships:"
For Each rel In CurrentDb.Relations
If Not rel.Attributes And dbRelationInherited Then
Debug.Print rel.Name
End If
Next rel

Unless you have an extremely large number of relationships I think the
list
will be accommodated in the debug window. If not you could amend the code
to
insert the name and relationship type into columns in an empty table.

Ken Sheridan
Stafford, England

 

[Tom Ventouris]

Thank you Aaron.
I am aware of the SQL server advantages and your views on this isuue which
are vigoroulsy and frequently expressed in this newsgroup.
It is not my intention to join those who debate your opinion, but I do have
an opinion of my own regarding SQL Server, based on my present needs - no
need to upsise.
It is not my intention to debate the pro's and con's of each of these
tools.(Access and SQL Server)

I am aware that there are tools far more flexible than MSAccess with or
without SQL Server, although I cannot claim to undertsand many of these.
Just as I would not boil water in a ten gallon pot for one cup of coffee, I
would not use any of these altenratives for what I do with Access.
Split MSAccess 2007 suits me fine and caters for all my needs in the
development and maintennace, (and recently, free deployment with Runtime
2007) of my 38 applications currently in use.
The applications I develop may not be the biggest or most complex around,
but they are fit for purpose.

I will welcome any advice you can offer which might assit me with this or
any future posts asking for help, in any form that does not require me to
trade in my current data structures for something other than my ACCDB BE.

I will post again and request specific advice on upsizing to SQL Server when
the need arises. I hope that you will take up the challenge to guide me when
that time comes.

if you upsized the database to SQL Server; you wouldn't have to deal
with this mess.

Access can't even enforce relationships between databases.

-Aaron

 

[Rick Brandt]

if you upsized the database to SQL Server; you wouldn't have to deal
with this mess.

Access can't even enforce relationships between databases.

Neither can SQL Server. You can fake it with triggers, but you cannot have
a foreign key that references a table in another database.