CHAPTER 18

CHAPTER 18

Accessing External Data

As you build your Microsoft Access application, you may need to access external data stored in another type of file. For example, you may want to access data stored in another database, a spreadsheet, a text file, or a Hypertext Markup Language (HTML) file. The Microsoft Jet database engine includes a number of installable ISAM and Open Database Connectivity (ODBC) drivers that you can use to access data stored in these external data sources. This chapter discusses strategies and techniques for accessing external data through installable ISAMs and ODBC.

External Data Sources

From your Microsoft Access application, you can access data from external data sources, such as database files, spreadsheets, text files, and HTML files. Microsoft Access can use data from any of the following external data sources:

Microsoft Access databases, and other databases that use the Microsoft Jet database engine, such as those created by Visual Basic, Visual C++, or Microsoft Excel. (This includes databases created with previous versions of Microsoft Access.)

Microsoft FoxPro, versions 2.0, 2.5, 2.6, 3.0 (read-only), and DBC

dBASE III®, dBASE IV®, and dBASE version 5.0

Paradox, versions 3.x, 4.x, and 5.x

Microsoft Excel version 3.0, 4.0, 5.0, 7.0, and 8.0 worksheets

Lotus® 1-2-3® WK1, WK3, and WKS spreadsheets

Delimited and fixed-width text files in tabular format

Tabular data in Hypertext Markup Language (HTML) files

Open Database Connectivity (ODBC) databases, such as Microsoft SQL Server versions 4.2 and later

External data must be in tabular format. Therefore, the term table is used to refer to a given set of external data, whether it resides in a database table, a spreadsheet, a text file, or an HTML file.

If the external data source has multiuser read/write permissions associated with it, you can view and update the data while other users access the external data source in its native application.

Important To access data from external data sources, Microsoft Jet uses one of several installable ISAM or ODBC drivers. Before you can access external data, you need to make sure that you have installed the appropriate drivers. For information on how to install drivers, search the Help index for "installing drivers."

Additionally, for each external data source that you want to connect to using ODBC, you must set up an ODBC data source by using the ODBC Manager. To start the ODBC Manager, double-click 32bit ODBC in Windows Control Panel.

External Data Access Choices

There are three ways you can access external data from your Microsoft Access application. You can:

Link the external table to your Microsoft Access application.

Open the external table directly from your Microsoft Access application.

Import the external table into your Microsoft Access application.

You can use a linked table just like you would use any other table in your Microsoft Access database. For example, you can create forms, reports, and queries that use the external table. Keep in mind that performance may be slightly slower with linked tables than with regular Microsoft Access tables due to time spent connecting to and retrieving external data.

Microsoft Access uses a different icon in the Database window for tables from each type of external data source. The different icons distinguish linked tables from tables that are stored in the current database. The icon that represents a linked table remains in the Database window along with tables in the current database, so you can open the table whenever you want to. If you delete the icon for a linked table, you delete the link to the table, not the external table itself.

All of the information necessary to establish and maintain a connection to the linked table is stored within the table definition. Once you link a table, the link is maintained between sessions so it's always available until it's deleted. A session begins when a user opens a Microsoft Access database, and ends when the user closes the database.

When you open an external table directly, the connection information isn't stored with the table definition; you must specify connection information each time you open the table. Because there isn't a link between the table and the Microsoft Access database, an icon for the table doesn't appear in the Database window.

In many cases, linking a table is a faster way to access external data than opening a table directly, especially when the data is located in an ODBC data source. If you're using external data in an ODBC data source and your particular situation requires that you open the table directly, keep in mind that performance will be significantly slower when you are using the data.

This chapter discusses accessing external data by either linking an external table or opening it directly.

See Also For more information on importing external data into your Microsoft Access database, search the Help index for "importing data."

Choosing the Best Data Access Method for Your Situation

To determine the data access method most appropriate for your situation, answer the following questions:

How frequently will you need to access the external table?

If you need frequent access to the table, use a link, because linked tables maintain all connection information between sessions. If you only need occasional access to the table, you can reduce the size of your Microsoft Access database by opening the table directly in code rather than storing the connection information in a link. If you need to access the table only one time to transfer the data to Microsoft Access, import the table.

Do you need to maintain access to the table from another application?

If you need to maintain access to the table from another application, such as its native application, you should link the table or open it directly rather than importing it.

Does the table reside on an ODBC data source?

If the table you want to access resides on an ODBC data source, you should always link the table rather than attempt to open it directly. In general, you won't need to import data from an ODBC data source.

Does the table reside on an installable ISAM data source?

If the table you want to access resides on an installable ISAM data source, you can either link the table or open it directly, depending on your application's needs.

Casa de Bender