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 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.mdb. When the procedure executes, the database window gains a link icon named CCSecureLinked. That linked table will contain the current contents of the external table:

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

After the preceding statement executes, the current database's database window will display 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 CCSecure table in C:\Secure Data\MOM Secure. mdb.

p MOM 2003 : Database (Access 2002 2003 file format) Open t^ Design J3New I X | >

3 Tables

Queries ^ Forms

Create table in Design view Create table by using wizard Create table by entering data CCSecureLinked Customers

3 Tables

Queries ^ Forms

Create table in Design view Create table by using wizard Create table by entering data CCSecureLinked Customers

CCSecure

.inked : Table

0(5

' ■ . ilijj

CJS^refefred

EQHolder

liÄSSifiiSFÄ

.ClIft-pireMon'

8KܧiSi:

(SikidCode |

-

K

11

Master Card

Tori Pines

M-1. . . ./rt

3

■:20[)&

653

I

13

Amex

Marilou Midt^lf

04361554525379

4

2007

956

16

Arne?«

Wlrnrr Wannabe

27434592338566

5

•20D8-

922

J

1?

Visa

Fjankly L nctu ujii

7103570604345

9

2006

232

22

Visa

7731976263732

1

2008

495

1

®

Ma&ter Card

Hank & tvtatild^-Siar

1748602421Z'95f'

8

2007

897

27

MasterCard

Scott and Nät-alie Sc

41570844560464

2

2006

323

28

Visa

Liri^' Pete.rson:

78591428318948

7

2007

749

29

MasterCard

Ino: Yäsfia

57744E203ES922.-

a

■:20D6

5781

30

Visa

i^üdney Pofeerts

Jt®l?49'<R6737

8

aoo^

786

31

Mastercard

fromirirc-Kryzwicki

6B,i5a9?09O232';

4

2009

MaslflfCard

RoSEirriaty ¡Stickler

42783489518193

6

2006

820

34

MasterCard

'tSdinünd ¿Cane

37002550170904

1

ffllBi

916

Maister Cwd

Kelley Moflkhouse

99130796449937

i !

2010

8361

36

Master-Card

John fijll'ler

74070934031796

3

2007

266

.37

¿rppv

Marsha Smi1

12

201 r

796

-

Record 1 M I

I . 1

_0>l ][.►*] of 23

The advantage of the linked table over the imported table is that the linked table shows live data from the external M0M2003Secure.mdb 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.)

0 0

Post a comment