Prev: show and save record number
Next: Test to help resolve problem with e-mail notifications
From: Photoliv41 on 28 Jan 2010 12:41
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!
From: John Spencer on 28 Jan 2010 12:55
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

Photoliv41 wrote:
> 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!
From: Jerry Whittle on 28 Jan 2010 13:28
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 isn't
a customer #111. Where do you send the order? Who do you bill? RI wouldn't
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's 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 doesn't 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's 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.


"Photoliv41" wrote:

> 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!
 | 
Pages: 1
Prev: show and save record number
Next: Test to help resolve problem with e-mail notifications