Chapter 20. Using External Data

IN THIS CHAPTER

• Why This Chapter Is Important
• Importing, Linking, and Opening Files: When and Why
• Importing External Data
• Creating a Link to External Data
• Opening an External Table
• Understanding Windows Registry Settings
• Using the Jet OLEDB:Link Provider String
• Working with Passwords
• Refreshing and Removing Links
• Looking at Special Considerations
• Troubleshooting
• Looking at Performance Considerations and Links
• Working with HTML Documents
• Practical Examples: Working with External Data from Within Your Application

Why This Chapter Is Important

Microsoft Access is capable of interfacing with data from other sources. It can use data from any OLE DB or ODBC data source, as well as data from FoxPro, dBASE, Paradox, Lotus, Excel, and many other sources. In this chapter, you learn how to interface with external data sources, with the user interface, and by using code.

External data is data stored outside the current database. It can refer to data stored in another Microsoft Access database, as well as data stored in a multitude of other file formats—including ISAM, spreadsheet, ASCII, and more. This chapter focuses on accessing data sources other than ODBC and OLE DB. ODBC and OLE DB data sources are discussed briefly in Chapter 22, “Developing Multiuser and Enterprise Applications.” They are covered in extensive detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

Access is an excellent front-end product, which means that it provides a powerful and effective means of presenting data—even data from external sources. Data is stored in places other than Access for many reasons. Large databases, for example, can be managed more effectively on a back-end database server such as Microsoft SQL Server. Data is often stored in a FoxPro, dBASE, or Paradox file format because the data is being used by a legacy application written in one of those environments. Text data is often downloaded from a mainframe. Regardless of the reason data is stored in another format, you must understand how to manipulate this external data in your VBA modules. With the capability to access data from other sources, you can create queries, forms, and reports.

When accessing external data, you have three choices: You can import the data into an Access database, access the data by linking to it from your Access database, or open a data source directly. Importing the data is the optimum route (except with ODBC data sources) but isn’t always possible. If you can’t import external data, you should link to external files because Microsoft Access maintains a lot of information about these linked files. This optimizes performance when manipulating the external files. Sometimes a particular situation warrants accessing the data directly. You therefore should know how to work with linked files, as well as how to open and manipulate files directly.

Importing, Linking, and Opening Files: When and Why

When you import data into an Access table, Access makes a copy of the data and places it in the Access table. After importing the data, Access treats it like any other native Access table. In fact, neither you nor Access has any way of knowing from where the data came. As a result, imported data offers the same performance and flexibility as any other Access table.

Linking to external data is quite different from importing data. Linked data remains in its native format. By establishing a link to the external data, you can build queries, forms, and reports that present the data. After you create a link to external data, the link remains permanently established unless you explicitly remove it. The linked table appears in the database window just like any other Access table, except that its icon is different. In fact, if the data source permits multiuser access, the users of your application can modify the data, as can the users of the applications written in the data source’s native database format (such as FoxPro, dBASE, or Paradox). The main difference between a linked and a native table is that you cannot modify the linked table’s structure from within Access.

Opening an external table is similar to linking to the table, except that a permanent relationship is not created. When you link to an external table, Access maintains connection information from session to session. When you open a table, you create a recordset from the table, and Access does not establish a permanent link to the data.

Selecting an Option

It is important that you understand when to import external data, when to link to external data, and when to open an external table directly. You should import external data in either of these circumstances:

• You are migrating an existing system into Access.
• You want to use external data to run a large volume of queries and reports, and you will not update the data. You want the added performance that native Access data provides.

When you are migrating an existing system to Access and you are ready to permanently migrate test or production data into your application, you import the tables into Access. You might also want to import external data if the data is downloaded from a mainframe into ASCII format on a regular basis, and you want to use the data for reports. Instead of attempting to link to the data and suffering the performance hits associated with such a link, you can import the data each time it is downloaded from the mainframe.

You should link to external data in any of the following circumstances:

• The data is used by a legacy application requiring the native file format.
• The data resides on an ODBC-compliant database server.
• You will access the data on a regular basis (making it prohibitive to keep the data up to date if it is not linked).

Often, you won’t have the time or resources to rewrite an application written in FoxPro, Paradox, or some other language. You might be developing additional applications that will share data with the legacy application, or you might want to use the strong querying and reporting capabilities of Access instead of developing queries and reports in the native environment.

By linking to the external data, users of existing applications can continue to work with the applications and their data. Your Access applications can retrieve and modify data without concern of corrupting, or in any other way harming, the data.

If the data resides in an ODBC database such as Microsoft SQL Server, you want to reap the data-retrieval benefits provided by a database server. By linking to the ODBC data source, you can take advantage of Access’s ease of use as a front-end tool, while taking advantage of client/server technology.

Finally, if you intend to access data on a regular basis, linking to the external table instead of temporarily opening the table directly provides you with ease of use and performance benefits. After you create the link, in most cases, Access treats the table just like any other Access table.

You should open an external table directly in either of these circumstances:

• You rarely need to establish a connection to the external data source.
• You have determined that performance actually improves by opening the data source directly.

If you rarely need to access the external data, opening that data directly might be appropriate. Links increase the size of your .ACCDB file. This size increase is not necessary if you rarely will access the data. Furthermore, in certain situations, when accessing Indexed Sequential Access Method (ISAM) data, you might find that opening the table directly provides better performance than linking to it.

Although this chapter covers the process of importing external data, this is essentially a one-time process and doesn’t require a lot of discussion. It is important to note, however, that after you import data into an Access table, it no longer is accessed by the application in its native format. The majority of this chapter focuses on linking to or directly opening external data tables.

Looking at Supported File Formats

Microsoft Access enables you to import, link to, and open files in these formats:

• Microsoft Access databases (including previous versions of Jet)
• ODBC databases
• SharePoint Lists
• HTML documents
• XML documents (import and open only)
• Microsoft Exchange/Outlook
• dBASE III, dBASE IV, and dBASE 5.0
• Paradox 3.x, 4.x, and 5.x
• Microsoft Excel spreadsheets
• Lotus WKS, WK1, WK3, and WK4 spreadsheets (import and open only)
• ASCII text files stored in a tabular format

Importing External Data

The process of importing external data is quite simple. You can import external data by using the user interface or by using VBA code. If you are planning to import the data only once or twice, you should use the user interface. If you are importing data on a regular basis (for example, from a downloaded mainframe file), you should write code that accomplishes the task transparently to the user.

Using the User Interface

To import an external data file using the user interface, follow these steps:

1. Click to select the External Data tab.
2. Select the appropriate import type by clicking the appropriate icon in the Import group. (Note that you can also click Saved Imports to perform an import that you have previously saved.) The dialog box that appears varies depending on the type of data you are importing. Figure 20.1 provides an example.

   Figure 20.1. The import dialog box varies depending on the type of import you are performing.

   

3. Notice in Figure 20.1 that you can opt to import the source data into a new table in the current database, append a copy of the records to the table, or link to the data source by creating a linked table. For this example, select the first option, Import the Source Data into a New Table in the Current Database.
4. Click Browse to locate the file that you want to import. The File Open dialog box appears.
5. Navigate to the file that you want to import and then click Open. Access returns you to the Get External Data – Excel Spreadsheet dialog box.
6. Click OK. Depending on the type of file you select, the import process finishes, or you see additional dialog boxes. If you select Excel Spreadsheet, for example, the Import Spreadsheet Wizard appears, as shown in Figure 20.2. This wizard walks you through the process of importing spreadsheet data.

   Figure 20.2. The Import Spreadsheet Wizard.

   

Using Code

The DoCmd object has three methods that assist you with importing external data: TransferDatabase, TransferText, and TransferSpreadsheet, each of which I cover in the following sections.

Importing Database Data Using Code

You use the TransferDatabase method of the DoCmd object to import data from a database such as FoxPro, dBASE, Paradox, or another Access database. Listing 20.1, included in basImport, shows an example that uses the TransferDatabase method.

Listing 20.1. Using the TransferDatabase Method

Table 20.1 lists the arguments for the TransferDatabase method.

Table 20.1. TransferDatabase Arguments

Importing Text Data Using Code

You use the TransferText method of the DoCmd object to import text from a text file. Listing 20.2 shows an example of this method.

Listing 20.2. Using the TransferText Method

Table 20.2 lists the arguments for the TransferText method.

Table 20.2. TransferText Arguments

Importing Spreadsheet Data Using Code

You use the TransferSpreadsheet method of the DoCmd object to import data from a spreadsheet file. Listing 20.3 shows an example that uses the TransferSpreadsheet method.

Listing 20.3. Using the TransferSpreadsheet Method

Table 20.3 lists the arguments for the TransferSpreadsheet method.

Table 20.3. TransferSpreadsheet Arguments

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. The following sections cover both alternatives.

One of the most common types of links is a link to another Access table. You create this type of link so that you can place the application objects (queries, forms, reports, macros, and modules) in a local database and so that you can store the tables in another database on a file server. Numerous benefits are associated with such a configuration. Chapter 22 discusses these benefits in more detail.

Using the User Interface

Creating a link using the user interface is very common. 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 you can establish them manually.

Creating Links Using the Database Splitter

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

To use the Database Splitter Wizard, follow these steps:

1. Switch to the Database Tools tab.
2. Click the Access Database button in the Move Data group on the ribbon. The Database Splitter Wizard appears (see Figure 20.3).

   Figure 20.3. The Database Splitter Wizard facilitates the process of splitting data into an application and database and a data database.

   

3. Click Split Database. The Create Back-End Database dialog box appears (see Figure 20.4). Here, you select a name and location for the database that will contain the table data.

   Figure 20.4. You use the Create Back-End Database dialog box to indicate the name and location of the database containing the data tables.

   

4. Make your selection and click Split. You should receive a message that the database successfully split.
5. Click OK to complete the process. If you look in the Navigation Pane, all the tables appear with an arrow, indicating that they are linked (see Figure 20.5).

   Figure 20.5. After you split the database, all tables appear with an arrow, indicating that they are linked.

   

Creating Links to Access Tables Manually

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

1. Click to select the External Data tab.
2. Click to select the Import Access Database button in the Import group. The Get External Data – Access Database dialog box appears, as shown in Figure 20.6.

   Figure 20.6. The Get External Data – Access Database dialog box allows you to designate whether you want to import or link to the Access tables.

   

3. Browse to locate the database containing the tables that you want to link to. The File Open dialog box appears.
4. Select the database containing the data tables and click Open.
5. Select Link to the Data Source by Creating a Linked Table.
6. Click OK. The Link Tables dialog box appears (see Figure 20.7).

   Figure 20.7. The Link Tables dialog box allows you to select the tables that you want to link to.

   

7. Select the tables to which you want to establish a link.
8. Click OK. The link process finishes. The Save Import Steps portion of the Get External Data Wizard appears.
9. Click Close to complete the process.

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:

1. Click to select the External Data tab.
2. Click the appropriate button in the Import group (for example, Excel). The Get External Data – Excel Spreadsheet dialog box appears.
3. Browse to locate the file that you want to link to. The File Open dialog box appears.
4. Select the file that you want to link to and click Open. Access returns you to the Get External Data – Excel Spreadsheet dialog box.
5. Select Link to the Data Source by Creating a Linked Table.
6. Click OK. If you selected Excel Spreadsheet, for example, the Link Spreadsheet Wizard appears.
7. Follow the steps of the wizard. (These steps vary quite a bit depending on the type of file you selected.)
8. Click Finish to complete the process. A dialog box appears, indicating that the process completed successfully. You will see the appropriate icon in the Navigation Pane, indicating the type of file you have linked to (see Figure 20.8).

   Figure 20.8. Notice that the Excel icon appears, indicating that the linked file is an Excel spreadsheet.

   

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:

1. Create a reference to the Microsoft ADO Extension 2.8 for DDL and Security (ADOX) library.
2. Create a Catalog object.
3. Set the Connection property of the Catalog object to the database that will contain the linked table.
4. Create a new Table object.
5. Set properties of the Table object.
6. Append the Table object to the Catalog.

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

Listing 20.4. Linking to an External Table

In Listing 20.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:

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_MACHINESOFTWAREMicrosoftJet4.0ISAM 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:DatabasesChap20Data"

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

In this case, the database called Chap20Data is stored on the database’s share of a particular file server.

Creating the Link

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

Listing 20.5. Establishing a Link to an External Table

Here is an example of how you call this subroutine:

Call LinkToDBase("c:customerdata","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 20.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 20.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 orthwind 2007.accdb","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).

Opening an External Table

Sometimes you will want to open, rather than link to, an external table. Linking provides ease of use when you are dealing with external tables. After you link to a table, you treat it just like any other Access table. The disadvantage of linking is that it uses ODBC. ODBC is not the most efficient means of interacting with a database for which you have a native OLE DB provider. Therefore, you might want to programmatically open an external table without creating a link to it. Opening an external table is a two-step process:

1. Establish a connection to the external data source.
2. Point a Recordset object at the result of executing a SQL statement against the Connection object.

Providing Connection Information

The connection information you provide when you open an external table is similar to the information you provide when you link to the table. The connection information is provided as the ConnectionString argument of the Open method of the Connection object. Here’s an example:

Here, the connection string is to the SQL Server database called Pubs on the local machine.

Opening the Table

You point the Recordset object at the result of executing a Select statement against the Connection object:

Set rst = cnn.execute("Select * from Authors")

Listing 20.7 shows what the entire process looks like in code.

Listing 20.7. Using the OpenDatabase Method

Listing 20.7 is called with this code:

Call OpenExternalSQL("Pubs","authors")

Notice that you are not appending a table definition here. Instead, you are creating a temporary recordset that refers to the external data. After you open the external table as a recordset, the code traverses through each record of the table, printing the value of the first field. Of course, after you open the recordset, you can manipulate it in any way you want. The table does not show up as a linked table in the Database window. In fact, when the routine completes and the local variable goes out of scope, the recordset no longer exists.

Now that you have seen how you can link to external tables as well as open them, you are ready to look at how you can refine both of these processes. Refining them involves learning the Windows Registry settings that affect the linking process, learning more about the parameters that are available to you when specifying connection information, learning how to specify passwords, learning how to refresh and remove links, and learning how to create an external table using VBA code.

Understanding Windows Registry Settings

Each ISAM driver has a separate key in the Windows Registry. These keys are located in the appropriate ISAM driver in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0ISAM Formats section of the Registry. These keys are used to configure the driver after initialization. As you can see in Figure 20.9, the setup program has created keys for several data sources. If you look at a specific data source (in this case, dBASE III), you can see all the settings that exist for the dBASE driver. The IndexFilter is set to dBASE Index(*.ndx), for example. At times, you will need to modify one of the Registry settings to customize the behavior of the ISAM driver; this is covered later in this chapter in the section “Looking at Special Considerations.”

Figure 20.9. The Windows Registry with keys for ISAM drivers.

Using the Jet OLEDB:Link Provider String

You use the Jet OLEDB:Link Provider string when linking to external tables. It includes the source database type, user ID, and password. You must use a semicolon to separate each part of the connection string.

Each source database type has a valid name. This is the name that must be used when accessing that type of data. These database types are found in the Windows Registry under HKEY_LOCAL_MACHINESoftwareMicrosoftJet4.0ISAM Formats. You must accurately specify the source database type; otherwise, you cannot access the external data.

The user ID is used whenever a username must be specified to successfully log on to the data source. This is most common when dealing with back-end databases such as Oracle, Sybase, or Microsoft SQL Server. This part of the provider string can be required to log on the user to the system where the source data resides. The UID keyword refers to the user ID.

As with the user ID, the password most often is included when dealing with back-end data. It can also be used on other database types that support passwords, such as Paradox, or when linking to an external Access table. The PWD keyword is used when specifying the password.

Finally, the dataset name refers to a defined ODBC data source. The DSN keyword refers to the dataset name in the connection string. The following is an example of a Jet OLEDB Link Provider string:

In the example, the SQL Server database being accessed is Pubs, the user ID is Alison, the Password is MyPass, and the data source name is PublisherData.

Working with Passwords

When working with passwords, you probably won’t want to hard-code a password into your application because that defeats the purpose of placing a password on your database. In Listing 20.8, the database’s password is included in the code, allowing the link to be made to the secured table without password validation.

Listing 20.8. Embedding a Database Password in Code

An invalid password results in a message appearing, requiring the user to log on. Unless you are using integrated security to log on to your database server, it is best to require the user to supply the password at runtime. In Listing 20.9, the code prompts the user for a password. The password entered by the user is used as part of the connection string.

Listing 20.9. Requiring Password Validation

Notice that the code retrieves the password from the user and stores it in a variable called strPassword. This strPassword variable is included in the connection string at runtime.

Refreshing and Removing Links

Refreshing links refers to updating the link to an external table. It is done when the location of an external table has changed. Removing links refers to permanently removing a link to an external table.

Access cannot find external tables if their locations have moved. You need to adjust for this in your VBA code. Furthermore, sometimes you might want to remove a link to external data—when you no longer need to use the data or when you have permanently imported the data into Access.

Updating Links That Have Moved

To refresh a link using VBA code, simply redefine the Jet OLEDB:Link Datasource. Listing 20.10 shows the code to refresh a link.

Listing 20.10. Refreshing a Link

You can modify this routine to prompt the user for the directory containing the data tables, as Listing 20.11 shows.

Listing 20.11. Prompting the User for the Database Path and Name

This routine points a Table object to the tblClients table. It then attempts to access the name of the first column in the table. If an error occurs, an input box prompts the user for the new location of the database. The routine modifies the Jet OLEDB:Link Datasource property for the database to incorporate the new location. It then resumes on the offending line of code. You should modify this routine to give the user a way out. Resume throws the user into an endless loop if the database is not available. An enhanced routine (see Listing 20.13) is presented later in the “Practical Examples” section of this chapter.

Deleting Links

To remove a link using VBA code, simply execute a Delete method of the Tables collection of a Catalog object connected to the database, as shown in Listing 20.12.

Listing 20.12. Removing a Link

Making a Local Table from a Linked Table

Access 2007 gives you the capability to convert a linked table to a local table. The process works like this:

1. Use the Navigation Pane to select the table you want to convert.
2. Click to select the linked table that you want to convert to a local table.
3. Click the Copy button in the Clipboard group on the Home tab.
4. Click the Paste button in the Clipboard group on the Home tab. The Paste Table As dialog box appears (see Figure 20.10).

   Figure 20.10. You use the Paste Table As dialog box to make a local table from a linked table.

   

5. Type a name for the new table.
6. Select Structure and Data (Local Table) to copy both the structure of the linked table and its data, or select Structure Only (Local Table) to copy only the structure of the linked table.
7. Click OK.

The linked table now appears as a local table within the current database.

Looking at Special Considerations

When you are dealing with different types of external files, various problems and issues arise. If you understand these stumbling blocks before they affect you, you will get a great head start in dealing with them.

dBASE

The major concerns you will have when dealing with dBASE files are deleted records, indexes, data types, and memo fields. When you delete a record from a dBASE table, Access does not remove it from the table. Instead, Access marks it for deletion. You must pack the database (a process in a dBASE table that removes deleted rows) for the records to actually be removed from the table. If records are deleted from a dBASE table using an Access application, the records are not removed. Because you cannot pack a dBASE database from within an Access application, the records still remain in the table. In fact, they are not automatically filtered from the Access table. To filter deleted records so that they cannot be seen within the Access application, you must set the Deleted value in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesXbase section of the Registry to 01 (True).

Access can use the dBASE indexes to improve performance. After you link to a dBASE table and select an index, an .INF file is created. This file has the same name as your dBASE database with an .INF extension. It contains information about all the indexes being used. Here’s an example of an .INF file:

[dBASE III]
NDX1=CUSTID.NDX
UNDX1=CUSTID.NDX

dBASE III is the database type identifier. NDX1 is an index number for the first index. The UNDX1 entry specifies a unique index.

The data types available in dBASE files are different from those available in Access files. It is important to understand how the field types are mapped. Table 20.4 shows how each dBASE data type is mapped to a Jet data type.

Table 20.4. Mapping of dBASE Data Types

Finally, make sure that you store the dBASE memo files in the same directory as the table. Otherwise, Access is unable to read the data in the memo file.

Text Data

When you are linking to an ASCII text file, Jet can determine the format of the file directly, or it can use a schema information file, which resides in the same directory as the text file. It always is named SCHEMA.INI, and it contains information about the format of the file, the column names, and the data types. The schema information file is optional for delimited files, but it is required for fixed length files. It is important to understand that ASCII files can never be opened for shared use.

Troubleshooting

Unfortunately, working with external data is not always a smooth process. Many things can go wrong, including connection problems and a lack of temporary disk space.

Connection Problems

Difficulties with accessing external data can be caused by any of the following circumstances:

• The server on which the external data is stored is down.
• The user does not have rights to the directory in which the external data is stored.
• The user does not have rights to the external data source.
• The external data source was moved.
• The UNC path or network share name was modified.
• The connection string is incorrect.
• The installable ISAM driver has not been installed.

Temp Space

Access requires a significant amount of disk space to run complex queries on large tables. This disk space is required whether the tables are linked tables stored remotely in another format, or they reside on the local machine. The application behaves unpredictably if not enough disk space is available to run the query. It is therefore necessary to make sure that all users have enough disk space to meet the requirements of the queries that are run.

Looking at Performance Considerations and Links

Because your application has to go through an extra translation layer (the installable ISAM), performance is not nearly as good with ISAM files as it is with native Jet data. (The exception to this is using ODBC to connect to SQL Server data.) It’s always best to import ISAM data whenever possible. If it’s not possible to import the data, you need to accept the performance that linking offers or consider linking the best solution to an otherwise unsolvable problem.

Working with HTML Documents

Access 2007 enables you to import, export, and link to HTML documents. Although working with HTML documents is similar to working with other files types, HTML documents deserve special mention. To import an HTML document, follow these steps:

1. Click to select the External Data tab.
2. Use the More option in the Import group to select HTML Document.
3. Browse to select the document you want to import and click Open. Access returns you to the Get External Data – HTML Document dialog box.
4. Click Import the Source Data into a New Table in the Current Database.
5. Click OK to continue. The Import HTML Wizard appears (see Figure 20.11).

   Figure 20.11. The Import HTML Wizard allows you to specify the details of the import process.

   

6. The first step of the wizard attempts to parse the HTML document into fields. You can accept what the wizard has done or click Advanced. When you click Advanced, the Import Specification dialog box that appears enables you to designate field names, data types, and indexes for each field and to select any fields you want to eliminate from the imported file (see Figure 20.12). This dialog box also enables you to modify the date order, date delimiter, and more.

   Figure 20.12. The Clients Import Specification dialog box enables you to designate the specifics of the import.

   

7. After you make any required changes to the import specifications, click OK to return to the Import HTML Wizard.
8. If appropriate, click First Row Contains Column headings. Then click Next.
9. Designate a field name, data type, and index for each field, as shown in Figure 20.13. Make any desired changes here and click Next.

   Figure 20.13. Customizing the properties of each imported field.

   

10. The next step of the wizard enables you to indicate that you want Access to add a primary key to the table, that you want to select your own primary key, or that you don’t want the imported table to have a primary key (see Figure 20.14). Make your selection and click Next.

    Figure 20.14. In this step of the wizard, you can add a primary key to the table.

    

11. The final step of the wizard enables you to assign a name to the table. You even can have the wizard analyze the table after importing it. Click Finish after you make your selection.

You can not only import an HTML document, but also link to one. To link to an HTML document, follow these steps:

1. Click to select the External Data tab.
2. Use the More option in the Import group to select HTML Document.
3. Browse to select the document you want to link to and click Open. Access returns you to the Get External Data – HTML Document dialog box.
4. Indicate that you want to link to the data source by creating a linked table.
5. Click OK to continue. The Link HTML Wizard appears.
6. Click the Advanced button to modify any link specifications and return to the first step of the wizard. Click Next to move to the second step of the wizard.
7. Specify information about each field you are linking to. Make any required changes and click Next.
8. Supply a name for the linked table and click Finish.

Although an imported HTML document acts like any other Access table, you can’t modify the data in a linked HTML document from within Access. You can use the linked document to create queries, reports, and forms.

Practical Examples: Working with External Data from Within Your Application

It’s time to split the data tables from the remainder of the application objects. You can easily accomplish this using the Database Splitter. After you split the tables from the rest of the database objects, you need to write code to refresh links. Both of these topics are covered in the following sections.

Splitting the Database Using the Database Splitter

Begin by using the Database Splitter to separate the tables from the rest of the database objects. You can find the CHAP20EX.ACCDB and CHAP20DATA.ACCDB files included on the sample code website. The CHAP20DATA.ACCDB file contains all the tables, and CHAP20EX.ACCDB contains the rest of the database objects.

Refreshing Links

If you distribute your application and all users do not have the same path to the CHAP20DATA.ACCDB file, the application will not load successfully. The LinkTables routine, located in the Switchboard startup form, ensures that the tables are successfully linked, as Listing 20.13 shows.

Listing 20.13. Loading the Application and Checking Table Attachments

Notice that the VerifyLink routine is called from the LinkTables routine. The VerifyLink routine, which tests to see whether any table links are broken, appears in Listing 20.14.

Listing 20.14. The VerifyLink Routine

The routine begins by pointing the ActiveConnection property of the Catalog object to the connection associated with the current database. It then loops through each table in the Tables collection of the Catalog object. If the table is a linked table, it attempts to access the name of the first column in the table. If any of the links are broken, an error occurs, and the For...Each loop is exited. If no error occurs, the function returns True; otherwise, the function returns False.

If the VerifyLink routine returns False, the ReLink routine, which attempts to reestablish the broken links, is called. Listing 20.15 shows the ReLink routine.

Listing 20.15. The ReLink Routine

The ReLink function receives two parameters. The first parameter is the name of the database the function will attempt to link to. The second parameter is a Boolean variable that designates whether the database is considered the default database.

The function begins by modifying the status bar. It then creates a Catalog object and an instance of a custom class called DBInfo. Class modules are covered in Chapter 14, “Exploiting the Power of Class Modules.” The ActiveConnection property of the Catalog object is set equal to the Connection property of the current project. Next, the FullName property of the DBInfo class is set equal to the name of the file that is passed as a parameter to the function. The DBInfo class extracts the path and the filename from the full filename. Just as with the VerifyLink function, the ReLink function uses a For...Next loop to loop through all the tables in the database. As it loops through each table, it attempts to establish a link to a database with the name passed as a parameter to the ReLink function.

This ReLink function is called twice from the LinkTables routine, shown in Listing 20.13. The first time it’s passed, the FullName property of the CurrentProject object and the Boolean are True, indicating that it will try to locate the table in a database with the same location as the application database. If that attempt is not successful, the LinkTables routine uses the FileDialog object to display the File Open dialog box, allowing the user to attempt to locate the database. The ReLink function is called again, searching for the table in the database selected by the user. If it is still unsuccessful, the routine quits the application.

Summary

The capability to link to external data is one of Access 2007’s strongest attributes. It is important that you understand how to link to external data via the user interface and by using VBA code. This chapter taught you how to link to external tables, open external data sources directly, refresh and remove links, and create external tables using VBA code. Many of the techniques covered in this chapter are covered extensively in Alison Balter’s Mastering Access 2002 Enterprise Development.