What is the point of linking tables?

[JString]

I've been playing around with this a bit and I can't figure out how this
would be very helpful at all.

It would be nice if I could split my bigger tables into a network of smaller
linked and synchronized tables, but when I try it the linked tables down the
line never get updated... each one has to be updated manually. Am I doing
something wrong here or am I just missing the point?

 

[Douglas J. Steele]

It's a fundamental concept of Access to split all applications into a
front-end (containing the queries, forms, reports, macros and modules),
linked to a back-end (containing the tables and relations).

In that way, you can make changes to the application (new forms, new
reports, etc) without having to worry about the fact that users have been
updating the data since you made your development copy.

It's pretty much essential that you split your application in that way for a
multi-user application. Only the back-end would exist on the server: each
user would have his/her own copy of the front-end, ideally on his/her hard
drive, to minimize the chance of database corruption.

 

[Larry Daugherty]

In order to develop and maintain applications they should be split
into FrontEnd and BackEnd. Once the application approaches stability,
most of the changes will be in the FrontEnd only. That means that the
FrontEnd can be maintained and enhanced by the developer (you) without
affecting the user(s). That also means that when the FrontEnd has
been changed and distributed again to the user(s) each user's copy can
be re-linked to the production data in the BackEnd.

As an educational exercise you might do the following:

1. Create MyTables.mdb. In that mdb create one or two tables and
nothing else.

2. Create MyFrontEnd.mdb. Before you do anything else, get
external data, Link to the table(s) in MyTables.mdb.

3. Still in MyFrontEnd.mdb, click on the Autoform Wizard Icon and
let it help you create a form. At the end of that creation process
switch to Run mode.

4. Enter several records of data using your new form.

5. Navigate back and forth among the new records you've just
created. The fact that you can see the different records means that
they're in the table in MyTables.mdb.

7. To verify the above, close MyFrontEnd.mdb and open MyTables.mdb.
Open the table on which your form was based. You will see the records
you just entered.
------------------------------------------

There are a couple of newsgroups that are particularly useful to new
Access developers:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

There is also a very useful web site: www.mvps.org/access

HTH

 

[Rick Brandt]

I've been playing around with this a bit and I can't figure out how
this would be very helpful at all.

It would be nice if I could split my bigger tables into a network of
smaller linked and synchronized tables, but when I try it the linked
tables down the line never get updated... each one has to be updated
manually. Am I doing something wrong here or am I just missing the
point?

You write "linked tables", but your description sounds more like "tables
with relationships between them". If the latter you are right.
Relationships do not automatically cause records to appear in one table
simply because you have added records in another.

When tables are related with referential integrity enforced then you can
optionally enable "cascade updates" and "cascade deletes". A cascaded
update means that if you change the primary key value in one table that
change will be cascaded to the foreign key fields of the tables on the other
side of the relationship. In this case it is only the key field that is
automatically updated.

A cascaded delete would cause a deletion of a record in the parent table to
also delete all of the related records in the tables on the other side of
the relationship.

However; the relationship would never cause a record created in one of the
tables to automatically create a record in any of the related related
tables. Relationships are about data integrity and enforcing "rules" upon
the data. They do not create data for you.

 

[JString]

Yes that is exactly what I'm talking about.

So the main purpose is to *stop* a user from entering data in a disorganized
way? I think I see what you're talking about, but I was hoping that I would
not have to add too many more features in order to synchronize my tables.

My main problem at the moment is that I don't know what the standard
practice is for working with complex relationships. I've created a table
called 'case files' which will contain the majority of the data that users
will be working with. Its records are organized by a composite key using
three ID fields from three other tables. What's the best way to go about
retreiving the ID's and populating these fields when new records are created?

 

[rquintal]

Yes that is exactly what I'm talking about.  

So the main purpose is to *stop* a user from entering data in a disorganized
way?  I think I see what you're talking about, but I was hoping that I would
not have to add too many more features in order to synchronize my tables.

My main problem at the moment is that I don't know what the standard
practice is for working with complex relationships.  I've created a table
called 'case files' which will contain the majority of the data that users
will be working with.  Its records are organized by a composite key using
three ID fields from three other tables.  What's the best way to go about
retreiving the ID's and populating these fields when new records are created?

In one word, comboboxes

Design a form for your 'case files' that uses a combobox for each of
the three tables, which will display the description from the table,
but is bound to the priimary key of each of those source tables.

Use the NorthWind Traders database supplied with Access as a guide.

 

[David W. Fenton]

So the main purpose is to *stop* a user from entering data in a
disorganized way? I think I see what you're talking about, but I
was hoping that I would not have to add too many more features in
order to synchronize my tables.

There shouldn't *be* any data that needs to be synchronized between
tables. That's the point of relational design, you put each piece of
data in only one place, and the relationships allow you to get to it
from there.

 

[Rick Brandt]

Yes that is exactly what I'm talking about.

So the main purpose is to *stop* a user from entering data in a
disorganized way? I think I see what you're talking about, but I was
hoping that I would not have to add too many more features in order
to synchronize my tables.

My main problem at the moment is that I don't know what the standard
practice is for working with complex relationships. I've created a
table called 'case files' which will contain the majority of the data
that users will be working with. Its records are organized by a
composite key using three ID fields from three other tables. What's
the best way to go about retreiving the ID's and populating these
fields when new records are created?

Usign subforms is the easiest way to do it. The linking properties of the
subform control will automatically push the linking key values into any new
records created in the subform.