DataBase - What is the difference between table relationships and links?

Asked By Photoliv41

28-Jan-10 12:41 PM

I am new to Access & need to understand the differences between a link & a
relat  ionship to a table or database.  I am told the table must be created
before the database, but I need several tables for my data.  I need to ensure
I use the correct method to allow me to create a database using the various
connected tables.

Please help!

28-Jan-10 12:55 PM

A link to a table is basically a connection path to a source of data that
exists somewhere other than the current Access application.  The data source
can be to a table in an MS SQL Server, Oracle, a text file, an Excel sheet, or
another Access application or other sources.

A relationship is how two tables (or other data sources) are related to each
other and can control whether or not data can be added to one table based on
whether or not corresponding data exists in a related table.  It also can
control whether or not you can delete records in one table based on whether or
not corresponding data exists in another table.

That is a VERY, VERY short overview of what relationships are.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

28-Jan-10 01:28 PM

That's a very good question.

A table holds data. You can create tables within an Access database. Once
you create multiple tables in a database file, you can join them in the
Relationship Window and enforce Referential Integrity.

Consider this: You have two table - Customers and Orders. A customer can
have no, one, or many orders. This is known as a One to Many 1-M
relationship. 1-M ( of in Access 1-infinity symbol) is what you want to see
in a relational database.

Referential Integrity (RI) comes in to stop a serious error. Let's say that
someone enters in a new order and puts in Customer# 111. However there is not
a customer #111. Where do you send the order? Who do you bill?  RI would not
allow 111 to be put into the Orders table as it first checks that there is a
111 in the Customer table.

Now it is possible to link to a table. Say there is another database or even
Excel spreadsheet with the Customer data in it. No use having the customer
data in Shipping, Sales, and Finances databases. If Sales finds out that the
customer has moved, but does not inform Shipping and Finance, there can be a
problem. Therefore such data might be kept in one centralized database.

However there is a little downside to storing some data in different
databases. You can enable RI to linked tables. Therefore if it is really
important, you may need to write VB code in your forms to ensure that there
is a Customer# 111 before saving the record in your database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.