Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Creating a Link to External Data

If you need to keep the data in its original format but want to treat the data just like any other Access table, linking is the best solution. All the information required to establish and maintain the connection to the remote data source is stored within the linked table definition. You can create links through the user interface and by using code. This section covers both alternatives.Chapter 20 discusses these benefits in more detail.

Using the User Interface

It is very common to create a link using the user interface. If you know what links you want to establish at design time, this is probably the easiest way to establish links to external data. You can establish links using the Database Splitter or manually.

Creating Links Using the Database Splitter

The Database Splitter was designed to split databases that already have been built with the tables and other database objects all in one physical MDB database file. It automates the process of moving the data tables to another database. The Database Splitter is covered in detail in Chapter 20.

NOTE

You can create links to tables only from an Access database, not from an Access project.

Creating Links to Access Tables Manually

To create a link to an Access table, follow these steps:

Creating Links to Other Types of Tables Manually

The process of creating links to other types of database files is a little different. It works like this:

NOTE

You must install the Jet 4.0 Service Pack 5 or have the BDE (Borland Database Engine) for the Select Unique Record Identifier dialog box to appear. If you do not have one of these tools, the data in the database cannot be edited. For more information, see the Microsoft Knowledge Base article Q283294.

Notice the icon indicating the type of file you linked to, as shown in Figure 19.8. You can now view the table data, build queries and reports from the data, and more!

NOTE

Earlier versions of Access supported links to FoxPro files using the FoxPro ISAM driver. With Jet 4.0, you must link to FoxPro tables using the Visual FoxPro ODBC Driver.

Using Code

Creating a link to an external table using code is a six-step process. Here are the steps involved in establishing the link:

Listing 19.4 shows the code for linking to an external table, which, in this case, exists in another Microsoft Access database.

Listing 19.4 Linking to an External Table

In Listing 19.4, a Catalog object is created. The ActiveConnection property of the Catalog object is pointed at the connection associated with the current database. Next, a Table object is created. The Name property of the Table object is set equal to tblLinkedTable. The ParentCatalog property of the Table object is set to point at the Catalog object. Four properties in the properties collection of the Table object are set to the appropriate values, and the Table object is appended to the Catalog object. This process is discussed in further detail in the following sections.

Providing Connection Information

When you link to an external table, you must provide information about the type, name, and location of the external database. You accomplish this by setting the following properties in the Properties collection of the Table object:

• Jet OLEDB:Link Provider String

• Jet OLEDB:Remote Table Name

• Jet OLEDB:Link Datasource

The following three lines of code illustrate the process of setting the provider string, name, and location of the source table:

tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
tbl.Properties("Jet OLEDB:Remote Table Name") = "tblClients"
tbl.Properties("Jet OLEDB:Link Datasource") = CurrentProject.Path & "\Chap19Data.mdb"

The Jet OLEDB:Link Provider is the ISAM format that will be used for the link. Each source database type is a different folder in the Windows registry. The folders are located in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats section of the registry. Valid source database types are as follows:

The Jet OLEDB:Link Datasource must include a fully qualified path to the file. You can specify the path with a drive letter and directory path or by using

universal naming conventions (UNCs) . For a local database, you must specify the path like this:

tbl.Properties("Jet OLEDB:Link Datasource") = "c:\Databases\Chap19Data"

For a file server, you can specify the UNC path or the drive letter path. The UNC path looks like this:

tbl.Properties("Jet OLEDB:Link Datasource") = _
"\\filesERVERNAME\Databases\Chap19Data"

In this case, the database called Chap19Data is stored on the databases share of a particular file server.

Creating the Link

Listing 19.5 shows how you put all the connection information together to establish a link to an external table.

Listing 19.5 Establishing a Link to an External Table

Here is an example of how you call this subroutine:

Call LinkToDBase("c:\customer\data","customer","tblCustomers")

The LinkToDBase subroutine receives three parameters:

• The name of the directory in which the dBASE file is stored

• The name of the file (the name of the table, without the DBF extension) to which you want to connect

• The name of the Access table that you are creating

The subroutine creates two object variables: a Catalog object variable and a Table object variable. It points the ActiveConnection property of the Catalog object variable at the connection associated with the current database. Next, it establishes properties of the Table object. The Link Datasource is the name of the directory within which the dBASE file is stored. The Link Provider String specifies that the type of table you are linking to is a dBASE III file. The Remote Table Name is the name of the dBASE file that you are linking to. After setting these properties, you are ready to append the table definition to the database.

You have seen how you can link to a dBASE table. Listing 19.6 puts together everything you have learned thus far in this chapter by creating a link to an Access table stored in another database.

Listing 19.6 Creating a Link to an Access Table Stored in Another Database

Notice that the Jet OLEDB Link Provider string no longer specifies the type of database to which you are connecting. Everything else in this routine is the same as the routine that connected to dBASE. Also, notice the parameters passed to this routine:

Call LinkToAccess("C:\databases\northwind.mdb","Customers","tblCustomers")

The database passed to the routine is an actual Access database (as opposed to a directory), and the table name is the name of the Access table in the other database (instead of the DBF filename).

NOTE

Whether you link to an external database using the user interface or code, you should always use the UNC path, rather than a drive letter. This ensures that all users with access to the network share are able to see the data, regardless of their drive letter mappings.