- database solutions and downloads for microsoft access

Linking External Microsoft Access Tables

How to create a macro that will link tables to external databases:

There are times when information needed is contained in other Access databases. For example, there may be one database that contains customer service information while another database contains customer finance information. The administrator may not want all of the information to reside in one database because the two databases are used by two different groups.

If information is linked from a primary database to a secondary database, when updates occur in the original table in the primary database the information automatically updates in the linked table in the secondary database. So, it is not necessary to re-link the tables in order to receive new information.

To create a macro in Microsoft Access that automatically links tables in two different databases, do the following:


  1. Click on the "Macros" option in the "Objects" menu
  2. Select "New" in the upper menu.
  3. Click in the "Action" field.
  4. Each time the macro is run a new table will be created unless the previous table with the same name is deleted. Using the drop-down arrow, select "DeleteObject" or type "DeleteObject" in the "Action" field.
  5. Press "F6" to go to the "Action Arguments" pane or click in the "Object Type" field and select the type of object that will be deleted. In this case, select "Table".
  6. Click in the "Object Name" field and use the drop-down arrow to select the name of the table or type the table’s name in the field.
  7. Click in the empty "Action" field or press "F6" to return to the "Action" field.
  8. Using the drop-down arrow, select "TransferDatabase" or type "TransferDatabase" in the "Action" field.
  9. Click "F6" to go to the "Action Arguments" pane or click in the "Transfer Type" field.
  10. In the "Transfer Type" field, select "Link". The databases will be linked. In this case, information will not be imported or exported.
  11. Click in the "Database Type" field and select "Microsoft Access" or type "Microsoft Access" into the field.
  12. In the "Database Name" field, type the full path to where the database with the original information resides. The "Zoom" option can be used in order to get additional space for typing the path. Right click in the "Database Name" field to select the "Zoom" option. If the database resides on a personal computer, use the following path: C:\Documents and Settings\John Doe\Desktop\Database1.mdb.

    However, if the database that the administrator is linking to resides on a server, use this path as an example, \\NA4705\Databases\DatabaseUsers\Project\Database2.mdb. Enter the full path including the name of the database and the .mdb extension. By entering the path, the administrator is telling Access exactly where to go to find the database.
  13. Click in the "Object Type" field. Select the type of object to link to. In this case, a table is being used, so select "Table". However, a link can be created to any other database object such as a report, query, or even another macro.
  14. Click in the "Source" field. Type the name of the object that is in the original database. For example, if the table in the primary database is entitled Customer Addresses then type "Customer Addresses" in the "Source" field.
  15. In the "Destination" field, enter the name of the linked table in the secondary database. This is the name that will be used for this linked table each time the macro runs. Ensure that the name that is assigned is not a table name currently being used.
  16. Click in the "Structure Only" field. In this case, the tables will be linked; so, the "Structure Only" field can be either "Yes" or "No". This field has no effect on linking. If the administrator was importing or exporting only the structure of the table (e.g., field names) then "Yes" would be selected. However, if s/he were to import or export the table’s data and structure then "No" would be selected.
  17. Save and name the macro by clicking the disk icon in the icon menu at the top of the screen.

Prior to running the macro, ensure that the linked table is closed. To run the macro without stopping, select the exclamation point icon (Run). To perform each action in the macro, use the icon to the right of the exclamation point (indented block paragraph with the arrow).