Practical concern with relationships

[Robin]

Access 2007 is of course a relational database and when putting together a
project the Tables are created and I relate them to each other. When the
queries are created they inherit the relationships from the underlying
tables, I believe. Just like the controls inherit their properties,
initially, from the tables as well.

So on the assumption that I use queries as a basis for every form and report
or other control. On a split database FE/BE converted to ACCDE/ACCDB does it
matter if the relationships are present in the BE? Or are they all
controlled from within the queries in the ACCDE?

I have updated a previous project and added new fields and some new tables.
As I update the existing BE's during my testing I ran across instances where
the program seems to run as anticipated but the BE does not even list the new
tables in the relationships window let alone carrying over any of the
relationships. I have the relationships window set to show all relationships
as well.

I intend to add the missing relationships prior to finishing up the
conversion of the client data but it still created the concerns listed above.

Thanks,
Robin

 

[Robin]

So if all of my, limited, projects are FE/BE I need not concern myself with
using any Referential Integrity options? Would appear to be a waste of time
if they can't be enforced.

Secondly, you're saying relationships ARE important in the BE even though
the FE diagram doesn't show them? I hope I got this part right for my
database's sake

I don't know how to create relationships in the FE apart from what I see in
the queries and these relationships just came along for the ride when I added
the tables I needed to the queries.

Which creates the question, if I change (I assume I can) a relationship in a
query does it have any effect or is the relationship of the underlying table
what controls the relationship?

Thanks,
Robin

 

[Tom Wickerath]

Chris,

Any changes to the back end relationships won't be reflected in the front end
relationship diagram, so the two relationship diagrams are bound to get out
of synch.

I cannot say that I have *ever* seen this to be the case. Perhaps this can
happen if a person fails to follow recommended practices of deleting linked
table(s) in the FE, compacting, and re-creating the table link(s) from
scratch (using File | Get External Data...|Linked tables) every time one
makes any design changes to the affected table(s) in the BE database.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robin]

Ok, a little further reading has left me with. The "relationships" I see in
the query design window are not relationships, but a representation of the
Joins, Inner and Outer, etc. Since the database behaves as expected because
the Joins control the data and the Tables just store it.

Beyond the benefit of having the Joins created automatically during query
creation/design. So when using FE/BE databases is there any reason to have
relationships created in the BE at all?

Thanks,
Robin

 

[Tom Wickerath]

Hi Robin,

So if all of my, limited, projects are FE/BE I need not concern myself with
using any Referential Integrity options?

Perhaps best answered by reading this newsgroup posting, which I have saved
to a Word document:
http://www.accessmvp.com/TWickerath/downloads/AreRelationshipsImportant.doc

Would appear to be a waste of time if they can't be enforced.

They will be enforced in the BE database, as long as the relationships are
between tables that are all within the BE database. Using RI is certainly not
a waste of time! (However, if you don't care about RI, then perhaps you'll be
happy to use SharePoint as an option, since you lose RI when using the
current release of SharePoint).

Secondly, you're saying relationships ARE important in the BE even though
the FE diagram doesn't show them?

See my previous reply to Chris, as far as having the relationships shown
(but not enforced) in the FE application.

I don't know how to create relationships in the FE...

You don't do this, unless the tables are located in the FE, in which case
they are not shared tables.

Which creates the question, if I change (I assume I can) a relationship in a
query does it have any effect or is the relationship of the underlying table
what controls the relationship?

No.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Albert D. Kallal]

So if all of my, limited, projects are FE/BE I need not concern myself
with
using any Referential Integrity options? Would appear to be a waste of
time
if they can't be enforced.

Secondly, you're saying relationships ARE important in the BE even though
the FE diagram doesn't show them? I hope I got this part right for my
database's sake

It has to be as per above. The back end is were the data is, and that's were
the RI has to be enforced.

Think about would/could happen if two different FE connected to the BE?
Which
would control things:

FE # 1
FE # 2
BE ????

You have 3 possible different places in the above to set RI. So, you mean we
really have no RI (referential integrity) at all because any old FE can
connect and break the rules we set?

The simple issue is that RI is enforced in the actual mdb file, end of
story. Thus, if you have a front end that connects to 2 different back
ends (perfectly legal to do this), then you can NOT have any kind of RI
between tables in the **different** back ends. So, while the trick of using
multiple back ends gets you around the 2 gig file size limit, you would
loose RI between the tables by using this trick...

I don't know how to create relationships in the FE apart from what I see
in
the queries and these relationships just came along for the ride when I
added
the tables I needed to the queries.

No, you ***always*** create new fields, new tables, new relationships in the
back end, and then go back to work in your fe. If you just added one new
table to the back end, then you simply use the "external" data tab, select
access and link the ONE table.

So, any structural changes to the data part such as adding fields,
relationships, increasing length of a field etc is going to be done in the
back end....

Which creates the question, if I change (I assume I can) a relationship in
a
query does it have any effect or is the relationship of the underlying
table
what controls the relationship?

No, just like query does not change or touch the original table, it simply
**pulls** data from that table. It changes nothing.

And, here is a previous post of mine explain the use of relationship in
access. In fact, 90% of your joins in the relationship window likely should
be left joins.....

===================

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

 

[Robin]

Thank you all for your responses, this is exactly the type of information I
needed. The resources you provide on this site are astounding.

I hope I was clear enough in my original post. My project has been FE/BE
from the beginning. I have used Relationships that enforced Referential
Integrity and a couple Cascade Update and a single Cascade Deletions, all
that I could determine were necessary. All of the original tables/fields and
new tables/fields are contained in the BE.

I will continue this practice after reading the information provided here.
I have no desire to reinvent the wheel after reading the articles provided.

I am in the process of testing the project prior to distribution and with
help and code provided by Peter Hibbs and Allen Browne I was able to add the
new tables and fields programatically. But I did not use all of the features
the code offered and neglected to add the relationships for my new tables. I
was concentrating on other aspects at the time.

But I noticed that the program still worked as expected, even without the
relationships present in the BE diagram. I did add the relationships and
edited the code to add them programatically as well.

Then I began to ask why it worked without the relationships and jumped to
the incorrect conclusion that the queries in the FE were actually controlling
the relationships. I now know that the query design view shows the Joins
rather than relationships.

Thank you,
Robin

 

[Tom Wickerath]

Hi Robin,

I hope I was clear enough in my original post. My project has been FE/BE
from the beginning.

Very good. In addition, each user should have their own copy of the FE
application file, running on their local hard drive, upon distribution. Is
this your current plan?

I was able to add the new tables and fields programatically.

There should be very little need to do this type of thing on a regular basis.

Then I began to ask why it worked without the relationships...

It may have worked without the relationships, but I doubt it worked as
efficiently as it could (unless you had each of your foreign keys indexed).
Take a look at my Multiuser Applications paper, and in particular the section
on using JET ShowPlan. My guess is that without relationships, any queries
you ran involved full table scans. Depending on the number of records in each
table in the BE database, this may or may not have made a noticeable
difference. However, with lots of records (say, 1000 or more) in a given
table, this can make a very significant difference, as soon as you have a
network that separates you from your data. I'm guessing [perhaps incorrectly]
that your testing to date has involved a split application, but with both the
FE and BE on your local hard drive.

Note: When you create a relationship with enforced referential integrity
(RI), there is no need to index the foreign key field separately. JET will
apply a new hidden index on this field, as long as you have RI enforced.

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Good Luck with a successful rollout of your application!

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robin]

Hi Tom,

The users each have a FE installed on their machine and the BE is on the
server. There are typically 3-7 users that will access the same BE data at
a time. There are 6 different locations currently each using a different BE,
which will never be shared or co-mingled.

The only reason for updating the data programatically is that some of the
offices are out of state and recently they have become a little "greedy" in
their desire for upgrades and new features. As this always includes items I
can't anticipate in advance it involves updating the BE fields. I figured if
I could simplify the distribution process a bit it would make it easier to
accomodate their lust for features.

You are correct that my current testing has been with FE/BE on the same
machine so there was no noticeable performance drop. It never occurred to me
that I'd get a more accurate representation by putting my "sample" BE on
another network machine to duplicate the same time stamp that the end user's
would experience.

Thanks for the tips, I'm almost afraid to read the Multi-User deployment
article for fear I may have to start anew and never get to un-veil my latest
revisions

Robin

Hi Robin,

I hope I was clear enough in my original post. My project has been FE/BE
from the beginning.

Very good. In addition, each user should have their own copy of the FE
application file, running on their local hard drive, upon distribution. Is
this your current plan?

I was able to add the new tables and fields programatically.

There should be very little need to do this type of thing on a regular basis.

Then I began to ask why it worked without the relationships...

It may have worked without the relationships, but I doubt it worked as
efficiently as it could (unless you had each of your foreign keys indexed).
Take a look at my Multiuser Applications paper, and in particular the section
on using JET ShowPlan. My guess is that without relationships, any queries
you ran involved full table scans. Depending on the number of records in each
table in the BE database, this may or may not have made a noticeable
difference. However, with lots of records (say, 1000 or more) in a given
table, this can make a very significant difference, as soon as you have a
network that separates you from your data. I'm guessing [perhaps incorrectly]
that your testing to date has involved a split application, but with both the
FE and BE on your local hard drive.

Note: When you create a relationship with enforced referential integrity
(RI), there is no need to index the foreign key field separately. JET will
apply a new hidden index on this field, as long as you have RI enforced.

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Good Luck with a successful rollout of your application!

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thank you all for your responses, this is exactly the type of information I
needed. The resources you provide on this site are astounding.

I hope I was clear enough in my original post. My project has been FE/BE
from the beginning. I have used Relationships that enforced Referential
Integrity and a couple Cascade Update and a single Cascade Deletions, all
that I could determine were necessary. All of the original tables/fields and
new tables/fields are contained in the BE.

I will continue this practice after reading the information provided here.
I have no desire to reinvent the wheel after reading the articles provided.

I am in the process of testing the project prior to distribution and with
help and code provided by Peter Hibbs and Allen Browne I was able to add the
new tables and fields programatically. But I did not use all of the features
the code offered and neglected to add the relationships for my new tables. I
was concentrating on other aspects at the time.

But I noticed that the program still worked as expected, even without the
relationships present in the BE diagram. I did add the relationships and
edited the code to add them programatically as well.

Then I began to ask why it worked without the relationships and jumped to
the incorrect conclusion that the queries in the FE were actually controlling
the relationships. I now know that the query design view shows the Joins
rather than relationships.

Thank you,
Robin

 

[Tom Wickerath]

Hi Robin,

Thanks for the tips, I'm almost afraid to read the Multi-User deployment
article for fear I may have to start anew and never get to un-veil my latest
revisions

Oh, I doubt it. It sounds to me like you are well on your way to success.
However, I would make sure that you have Name Autocorrect disabled, that you
remove subdatasheets, and that you verify that you have a persistant
connection established. Those three things should not take all that long. You
might also want to read the JET ShowPlan article, enable the registry
setting, and then use your application as you expect your users will. Inspect
the resulting ShowPlan.Out file for table scans. Don't get too concerned with
table scans on tables that include relatively few records, since a table scan
may very well be the most efficient way of getting the data in that case.
Also, if you have any combo boxes tied to lookup tables, these will surely
result in table scans if the combo boxes are unfiltered (a normal expected
result).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[david]

What you need for the database to work correctly is Primary Keys.
By default, Access automatically prompts you to create a primary
key for each table, so many people don't realise that some things
won't work without it. When you start creating tables in code,
you don't get prompted to create a primary key....

When you create a new table in the Access create-table window,
Access automatically adds indexes to fields that start with
ID;key;code;num
so sometimes you don't realise that you are using indexes when
you are. When you start adding fields in code, you don't automatically
get indexes...

When you create relationships in the (BE) relationship window,
Access automatically adds indexes to the fields in the relationship.
If you have indexed both fields and have a primary key, this may
not matter much, but it certainly can affect performance if you don't
have any indexes. If the relationship index is identical to an index
that has already been declared, you don't get duplicate indexes,
only multiple names, so there is not much overhead. However,
if the indexes are subtly different (fields in different order in a
multi-field index), you do get duplicate indexes, and some extra
indexing overhead, so you should be careful about indexing
and relationships with multiple fields. Also the same problem
with multi-field joins: the multi-field join should match the multi
field index should match the multi-field relationship.

(david)