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:
To successfully import and link external data in spreadsheets, text files, or HTML files, the 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 Data Source Administrator. To start the ODBC Data Source Administrator, double-click 32bit ODBC in Windows Control Panel.
There are three ways you can access external data from your Microsoft Access application. You can:
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.”
To determine the data access method most appropriate for your situation, answer the following questions:
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.
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.
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.
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.