Part III: More-Advanced Access Techniques
584
Tip
If you’ll be importing data from the same source frequently, you can automate the process with a macro or a
VBA procedure. This can be very helpful for those times when you have to import data from an external source
on a regular schedule or you have complex transformations that must be applied to the imported data.
When to export internal data
Exporting data enables you to pass data to other applications. By exporting data, Access automati-
cally converts data to the external format and copies it to a file that can be read by the external
application. As has already been mentioned, sometime you must import data into Access as
opposed to just linking to the external data source if you want to be able to modify the data. If you
still need to be able to work with the modified data in the external application, you have little
choice but to create a new file by exporting the modified data.A common reason to export data is to because you want to share the data with other users who
don’t have Access installed.Linking External Data
As the database market continues to grow, the need to work with information from many different
sources will escalate. If you have information captured in a SQL Server database or an old dBase
table, you don’t want to reenter the information from these sources into Access. Ideally, you want
to open an Access table containing the data and use the information in its native format, without
having to copy it or write a translation program to access it. In many cases, the capability of access-
ing information from one database format while working in another is often an essential starting
point for many business projects.Using code to copy or translate data from one application format to another is both time-consuming
and costly. The time it takes can mean the difference between success and failure. Therefore, you
want an intermediary between the different data sources in your environment.Although uncommon, you might occasionally need to work with data from a program that isn’t stored
in a supported external database or file format. In cases such as this, the programs usually can export or
convert their data into one of the formats recognized by Access. To use the data in these programs,
export it into a format recognized by Access and then import it into Access.For example, many applications can export to the dBASE file format. If the dBASE format is not avail-
able, most programs, even those on different operating systems, can export data to delimited or fixed-
width text files, which you can then import into Access.Working with data in unsupported programs