In some situations it is better to link to the
existing data instead of bringing it into the Access database. Linking
allows you to change the
data in only one place. Whatever program you use to view it, you
will see the most current data all of the time. You could still use the data
with the original program, for example, a spreadsheet program.
You can only link tables.
The icon in the Database Window for a linked table has an arrow and an
icon for the type of object it is.
Broken links: If you move or rename the
file to which you are linking or if you move the database that is doing
the linking, the links
break. You will
have to recreate the link(s). You must still have access to the
source file and know how to tell Access where it is from the location of
the Access database. This
will not work unless you have planned for it! You must think ahead before
deciding to link.
Where you are:
Project 1: Intro
Project 2: Access Basics
Project 3: Tables & Queries
Project 4: Forms & Reports
Special Forms & Reports
Methods of Linking
| | - Select a format (Access, dBASE, spreadsheet, text file, Paradox) and a file
| - Select a format (dBASE, spreadsheet, text file, Paradox) and a file
name. A new database is created which
automatically links to
If you select an Access database in the Open dialog, the current database
will close and the selected database will just open. No links are created
in this case.
Properties for a Linked Table
For most table and field properties you must make any changes to the source
table from inside the original program.
What you can change from the linked database:
- You can change the table's name in the new database.
- You can edit the
Format, DecimalPlaces, InputMask, and Caption properties for fields in a
linked table, but not the other properties.
- You can format the table's datasheet.
Work-around for some properties: Create a form and set the properties for the
controls that are bound to your fields. But... any properties that were
set in the source table, like a ValidationRule, are still in effect. So
Relationships: If you import more than one table from a
database, any relationships between them are also imported.
Step-by-Step: Link to Tables
What you will learn:
|to link to a table|
to modify a linked table
to break links
to repair broken links
You created a Projects database in
Project 3: Tables & Queries and saved it
to your Class disk. The new World Travel database has more data on the
staff than the Projects database has in its Staff table, but it does not
have any of the data on projects. You will link to the tables in the
Projects database and create a new relationship. The Staff ID numbers are
the same for both tables, or else this would not work well!
If necessary, open the database
worldtravel.mdb from the
databases project4 folder in your Class disk.
From the menu select | |
The Link dialog appears.
to your Class disk to the folder databases project3
and select the file projects.mdb.
Click on the Link button.
The Link Tables dialog appears.
the tables Projects and
Click on OK.
tables show in the Database Window as linked tables.
Linked tables that have a relationship still have that
relationship in the new database.
Click on the Relationships button
The Relationships window may be blank the since you have not created
any relationships yet in this database.
on the Show All Relationships button
The two linked tables already had a relationship as well as two of the
Click on the
Show Table button
to open the Show Table dialog, and
the following missing tables. Arrange them neatly, similar to the
Create the relationships shown in the
Staff on DivisionID
Staff on LocationID
StaffID with EmployeeID
Trips to Staff
matching AgentID with
Clients to Trips
Wrong fields were matched
You dragged a matching field from one table to another but dropped it
on the wrong field,
Solution: Click on the join line to select it. (It will get wider)
Press the Delete key. A confirmation message box appears. Click on OK to
continue to delete the relationship.
Note: When linking tables, you must be careful to match the correct fields and that those fields
have the correct data types. This is a particular problem with linked
tables, where mis-matches can easily occur.
Save button on the toolbar to save your changes.
Close the Relationships window.
Modify Linked Table
You can add data to a linked table and make some changes to the table
design, but not many. The properties Format, Input Mask, Decimal Places,
and Caption can be changed. The table's name can be changed, also.
the Projects table in datasheet view.
- Add a new record (#9) with the following data:
ProjectName = Update web site
ProjectDescription = Revise and improve site
Budget = $5000.00
This new record is saved in the source table in the Projects database.
Table Design View.
message appears telling you that the table is a linked table and that
you cannot modify some of its properties.
- Click on Yes to open the design view anyway.
- Inspect each of the properties for each of the fields. Most
show a warning at the right of the property:
the property Decimal Places for the Budget
field to zero.
A button appears for Property Update Options.
- Hover over the options button until the down arrow appears,
choices appear - to update this change everywhere the field Budget
is used or to open Help about the other choice.
on "Help on propagating field properties" and read the
article that appears. A field's properties which are set in the table's design are carried forward
to any controls on forms, reports, and data access pages that use that
- Close the Help window.
- Open the Property Update Options again and select the
first choice, Update Decimal Places....
message appears. No objects need to be updated. Well, of course not! You have
not yet created any forms, reports, or data access pages in this
database! But it's nice
to know how this works BEFORE creating a lot of objects.
- Click on OK to close the message box.
Break Links: Change Database Name
If the Projects database is renamed or moved, the links to its tables
- Open a My Computer window and navigate to the
folder on your Class disk where you saved the Projects database.
Do not open the database.
- Rename the file as projects2.mdb.
- Switch back to the Database Window for
the linked table Projects.
Whoops. A message appears telling you that Access cannot find the
file containing the linked table. Of course not! You changed its name.
The same message would appear if you moved the file to a different
folder or disk or if you moved the World Travel database or renamed it.
- Click on OK to close the message.
Repair Broken Links: Linked Table Manager
Access has a utility feature called Linked Table Manager to help you
repair the link. You just have to be able to find the database!
the menu select | | The Linked Table Manager dialog appears listing the linked
This dialog allows you to update the links individually if you wish. If
all the linked tables are from the same database, you can update them
all at once.
- Click the button Select All. A check mark appears
beside each linked table.
- Click on OK.
A dialog appears for you to select the correct database.
- Navigate to your Class disk to the folder
databases project3 and select
A message appears telling you that you were successful.
- Click on OK.
You are back in the Database Window.
- Open the linked table Projects.
Break Link: Change Table Name
Another way to break a link is to change the name of the table in the
- Open the database projects2.mdb from your Class disk.
- Rename the table Projects as
- Switch to the database worldtravel.mdb.
- Open the linked table Projects
An error message appears telling you that Access cannot find a table
with that name. You know that it found the database or the previous error
message would have appeared instead.
- Click on OK.
- Switch back the the Projects database and change the
table's name back to Projects.
When you do not want to change a
table's name back, you can use the Linked Tables Manager to update the link to
the new table name.
- Switch to the World Travel database and try to open
the linked table Projects again.
- Close the database Projects.