External data is data that is stored outside the current database. It can be located in another Microsoft Access database or in various other formats, such as ISAM, spreadsheets, ASCII text files, and others.
Microsoft Access acts as a powerful front-end application that allows you to work with external data and present it in the form of queries, forms, and reports. Sometimes data is stored outside of Access for various reasons: for managing large databases, it is more convenient to use servers such as Microsoft SQL Server; for compatibility with legacy applications, data can be stored in FoxPro, dBASE, or Paradox formats, and text data may come from mainframes or midrange computers.
It is important to be able to manage external data in Access in order to create queries, forms, and reports, even if the data is physically located elsewhere.
Importing or connecting external data
There are two main approaches to working with external data in Access:
Importing data — Access creates a copy of the external data in its own table. After importing, the data becomes completely “native” to Access: it can be edited, queried, and used in reports with the full power of Access. The data source is no longer relevant.
Connecting to external data (link) — the data remains in its original format, and Access creates a link to it. You can use this link to build queries, forms, and reports. The linked table appears in the Access navigation pane as a regular table, but its structure cannot be changed from Access. If the data source supports multi-user access, changes can also be made by users of the original application.
When to import and when to connect
Importing data is recommended in the following cases:
- When migrating an existing system to Access;
- When you need to use data for a large number of queries and reports without having to update it—this provides better performance.
- If data is regularly converted to text format (ASCII) and used for reports.
Connecting to external data is preferable when:
- The data is used by outdated applications and must remain in its native format;
- The data source is located on a server that supports ODBC (e.g., Microsoft SQL Server);
- Access to the data is required on an ongoing basis, and it is difficult to keep it up to date without a connection.
Connecting to external data allows Access to function as a front end, providing convenience and performance, while existing applications continue to use their data without risk of corruption.
Thus, the choice between importing and connecting depends on the tasks at hand: importing provides maximum performance and data autonomy in Access, while connecting allows you to work with up-to-date data in its native source format and maintain compatibility with other applications.
