MS Access - Data Export

In this chapter, we will understand how to export data from Access. Data export is actually the opposite of importing data. In importing data, we bring data from other formats in Access, while in exporting we save the data in other formats.

To understand what kind of data you can export from Access data, let us open your database and go to the External Data tab.

In the Export group, you can see the different kind of options available for data export from Access. Following are the most commonly used data export formats −

• Microsoft Office Excel
• Microsoft Office Access
• ODBC Databases (For example, SQL Server)
• Text files (delimited or fixed-width)
• XML Files

Example

Let us look at a simple example of data export from Access. Open your database where you want to export the data from. In the Navigation Pane, select the object that you want to export the data from.

You can export the data from table, query, form, and report objects etc. Let us select the qryAllProjects and then, go to the External Data tab.

On the External Data tab, click on the type of data that you want to export to. For example, to export data in a format that can be opened by Microsoft Excel, click Excel.

Access starts the Export wizard. In the wizard, you can set the information such as the destination file name and format, whether to include formatting and the layout, which records to export. Once you are done with the required information, click Ok.

On this screen of the Wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export steps check box and close the dialog box.

The following Excel file opens up.

Let us now suppose you want to export data to a text file, on the External Data tab, click on the Text File.

Specify the export options and click Ok. You will see the Encode dialog box, wherein we want to export the data in default encoding.

Select the first option and click Ok.

On this screen of the wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export steps check box and close the dialog box.

You will now see that the Text file is open.

Similarly, you can explore other options as well. Thus, it is highly recommended to play with other import export features too.