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:
How-to:
- Click on the "Macros" option in the "Objects"
menu
- Select "New" in the upper menu.
- Click in the "Action" field.
- 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.
- 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".
- 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.
- Click in the empty "Action" field or press "F6"
to return to the "Action" field.
- Using the drop-down arrow, select "TransferDatabase"
or type "TransferDatabase" in the "Action" field.
- Click "F6" to go to the "Action Arguments"
pane or click in the "Transfer Type" field.
- In the "Transfer Type" field, select "Link".
The databases will be linked. In this case, information will not
be imported or exported.
- Click in the "Database Type" field and select "Microsoft
Access" or type "Microsoft Access" into the field.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).