This article shows you what kinds of data you can import and export by using Access, and shows you the basic steps to get started with an import or export operation.
One of the most useful features of Access is its ability to interface with data from many other programs. In fact, it’s difficult to summarize in a single article all the ways in which you can move data into and out of Access. For example, here are just a few ways in which you might use the data-exchange features of Access:
Overview of external data operations in Access
In many programs, you use the Save As command to save a document in another format, so that you can open it in another program. In Access, however, the Save As command is not used in the same way. You can save Access objects as other Access objects, and you can save Access databases as earlier versions of Access databases, but you cannot save an Access database as, say, a spreadsheet file. Likewise, you cannot save a spreadsheet file as an Access file (.accdb). Instead, you use the commands on the External Data tab in Access to import or export data between other file formats.
NOTE You can also write macros or Visual Basic for Applications (VBA) code to automate the import and export operations that are available on the External Data tab.
Types of data that Access can import, link to, or export
A quick way to learn about the data formats that Access can import or export is to open a database and then explore the External Data tab on the ribbon.
If you don’t see the exact program or data type that you need, chances are your data can be exported by the other program into a format that Access understands. For example, most programs can export columnar data as delimited text, which is then easily imported into Access.
The following table shows which formats can be imported into, linked to, or exported out of Access:
|PROGRAM OR FORMAT||IMPORT ALLOWED?||LINKING ALLOWED?||EXPORTING ALLOWED?|
|Microsoft Office Excel||Yes||Yes||Yes|
|Microsoft Office Access||Yes||Yes||Yes|
|ODBC Databases (For example, SQL Server)||Yes||Yes||Yes|
|Text files (delimited or fixed-width)||Yes||Yes||Yes|
|PDF or XPS files||No||No||Yes|
|E-mail (file attachments)||No||No||Yes|
|Microsoft Office Word||No, but you can save a Word file as a text file and then import the text file.||No, but you can save a Word file as a text file and then link to the text file.||Yes (you can export as Word Merge or as Rich Text)|
|Data Services (see note)||No||Yes||No|
|Outlook Folders||Yes||Yes||No, but you can export as a text file, and then import the text file into Outlook.|
NOTE To enable the Data Services button, Microsoft .NET 3.5 or later must be installed.
The general process for importing or linking data is as follows:
NOTE It’s a good idea to look at your source data ahead of time so that you know the correct answers to these questions when the wizard asks for them.
After you have completed the wizard, Access notifies you of any problems that might have occurred during the import process. In some cases, Access might create a new table called ImportErrors, which contains any data that it was unable to import successfully. You can examine the data in this table to try to find out why the data did not import correctly.
For more information about importing or linking to data in a specific format, search the Access Help system for articles and videos that cover that format.
The general process for exporting data from Access is as follows:
For more information about exporting to a specific format, search the Access Help system for articles and videos that cover that format.