Forms & Reports:
Link to Tables

 Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

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.

Examples:
  Icon: Linked table Access table
  Icon: Linked Excel worksheet Excel worksheet
  Icon: Linked text file Text file
  Icon: Linked HTML page HTML page
  Icon: Linked dBase file dBase file

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:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/Link Arrow: subtopic open
    Icon: StepImport Spreadsheet
    Icon: StepImport Text File
    Icon: StepImport Access Objects
    Icon: StepLink
    Icon: StepExport from Access
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Search  
Glossary  
Appendix

Methods of Linking

  • Menu:
          File | Get External Data | Link tables - Select a format (Access, dBASE, spreadsheet, text file, Paradox) and a file name.
     
  • Menu:
          File | Open - Select a format (dBASE, spreadsheet, text file, Paradox) and a file name. A new database is created which automatically links to the data.
    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 be careful!

Relationships: If you import more than one table from a database, any relationships between them are also imported.

Icon: Step-by-Step 

Step-by-Step: Link to Tables

  Icon: Step-by-Step

What you will learn:

 to link to a table
to modify a linked table
to break links
to repair broken links

Start with:  Class disk, worldtravel.mdb, projects.mdb

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!

Link Tables

  1. If necessary, open the database worldtravel.mdb from the databases project4 folder in your Class disk.
     

  2. From the menu select  File | Get External Data | Link Tables...
    The Link dialog appears.
     

  3. Dialog: Link - projects.mdbNavigate to your Class disk to the folder databases project3 and select the file projects.mdb.
     

  4. Click on the Link button.
    The Link Tables dialog appears.
     

  5. Dialog: Link TablesSelect the tables Projects and ProjectStaff.
     

  6. Click on OK.
    Database Window: Linked tablesThe tables show in the Database Window as linked tables.
     

Relationships

Linked tables that have a relationship still have that relationship in the new database.

  1. Click on the Relationships button Button: Relationships (2003).
    The Relationships window may be blank the since you have not created any relationships yet in this database.
     

  2. Show All RelationshipsClick on the Show All Relationships button Button: Show All Relationships.
     
    The two linked tables already had a relationship as well as two of the imported tables.
     

  3. Click on the Show Table button Button: Show Table to open the Show Table dialog, and add the following missing tables. Arrange them neatly, similar to the illustration below:
    Clients
    Divisions
    Locations
    Staff
     

  4. Create the relationships shown in the illustration:
    Divisions to Staff on DivisionID
    Locations to Staff on LocationID
     ProjectStaff to Staff matching StaffID with EmployeeID
    Trips to Staff matching AgentID with StaffID
    Clients to Trips on ClientID
     Relationships
     
    Icon: TroubleProblem: 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.
     

  5. Icon: Save Click the Save button on the toolbar to save your changes.
     

  6. 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.

  1. Datasheet View: added record 9Open the Projects table in datasheet view.
     
  2. 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. Neat!
     
  3. Icon: Design Switch to Table Design View.
    Message: Table is linked with some properties that can't be modified.A message appears telling you that the table is a linked table and that you cannot modify some of its properties.
     
  4. Click on Yes to open the design view anyway.
     
  5. Inspect each of the properties for each of the fields. Most show a warning at the right of the property: Warning in Table Design View: This property cannot be modified in linked tables.. 
  6. Design View: no decimalsChange the property Decimal Places for the Budget field to zero.
     
    A button appears for Property Update Options.
     
  7. Hover over the options button until the down arrow appears, then click.
    Design View: Update Options - list openTwo choices appear -  to update this change everywhere the field Budget is used or to open Help about the other choice.
     
  8. Help window: How control properties relate to properties in their underlying fieldsClick 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 field.
     
  9. Close the Help window.
     
  10. Open the Property Update Options again and select the first choice, Update Decimal Places....
    Message: No objects needed to be updated.A 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.
     
  11. 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 will break.

  1. 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.
     
  2. Rename the file as projects2.mdb.
     
  3. Switch back to the Database Window for worldtravel.mdb.
     
  4. Message: Could not find file...Open 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.
     
  5. 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!

  1. Menu: Tools | Database Utilities | Linked Table ManagerFrom the menu select Tools | Database Utilities | Linked Table Manager
    The Linked Table Manager dialog appears listing the linked tables.
    Dialog: Linked Table Manager
    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.
     
  2. Click the button Select All. A check mark appears beside each linked table.
     
  3. Click on OK.
    A dialog appears for you to select the correct database.
     
  4. Navigate to your Class disk to the folder databases project3 and select projects2.mdb.
     
  5. Message: All selected linked tables were successfully refreshed.Click on Open.
    A message appears telling you that you were successful.
     
  6. Click on OK.
    You are back in the Database Window.
     
  7. Open the linked table Projects.
    Success!

Break Link: Change Table Name

Another way to break a link is to change the name of the table in the original database.

  1. Open the database projects2.mdb from your Class disk.
     
  2. Rename the table Projects as Project.
     
  3. Switch to the database worldtravel.mdb.
     
  4. 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.
    Message: The Microsoft Jet database engine cannot find the input table or query 'Projects'. Make sure it exists and that its name is spelled correctly.
     
  5. Click on OK.
     
  6. Switch back the the Projects database and change the table's name back to Projects.
    TipWhen 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.
     
  7. Switch to the World Travel database and try to open the linked table Projects again.
    Success!
     
  8. Close the database Projects.