Linking to External Data through Code

The TransferDatabase method of the VBA DoCmd object also provides a syntax for linking to an external table (but not to a query). Note that the first argument after TransferDatabase is acLink rather than acImport. Other than that, the syntax is basically the same:

DoCmd.TransferDatabase acLink, "Microsoft Access", "pathToExternalDB", acTable, _ "externalTbl", "localTableName"

For example, the DoCmd.TransferDatabase (as shown here) sets up a link from the current database to an external table named CCSecure in the database file named C:\SecureData\MOMSecure.accdb. When the procedure executes, the Navigation pane gains a link icon named CCSecureLinked. That linked table contains the current contents of the external table:

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\SecureData\MOMSecure.accdb", acTable, _ "CCSecure", "CCSecureLinked"

After the preceding statement executes, the current database's Navigation pane displays a new link icon named CCSecureLinked. The arrow in the icon shows that this is a linked table, identical to the kind of linked tables that the Database Splitter creates. Opening the link shows the contents of the external table, as in Figure 13-6.

Figure 13-6:

Result of linking to a

CCSecure table in C:

\Secure Data\MOM Secure, accdb.

Figure 13-6:

Result of linking to a

CCSecure table in C:

\Secure Data\MOM Secure, accdb.

The advantage of the linked table over the imported table is that the linked table shows live data from the external MOMSecure.accdb file. Therefore, if somebody changes the table data, from any database, those changes are reflected in the linked table. The main disadvantage is that data access slows down because the link requires some network traffic between the actual table and the local link.

The other disadvantage — at least within the context of this example — is that you can't link to a query. You have to link to a table. So the only choice here is to link to the table, thereby making all the table's fields visible in Datasheet view. (However, a query in the local database that gets its data from the linked table could still hide any information within that table.)

Was this article helpful?

0 0

Post a comment