your image

Access 2016: Export Data from Microsoft Access

quackit
Related Topic
:- MS Access

You can export data from Access to a variety of formats by using the External Data tab on the Ribbon.

Access 2016 includes options for exporting data to other sources. You can export to a variety of formats, including:

  • Excel file
  • Text file (CSV, TSV, etc)
  • XML file
  • PDF file
  • XPS file
  • Email
  • Word document
  • Sharepoint list
  • HTML document
  • Another Access database
  • ODBC datasource (such as SQL Server)

You can export from a table, a query, a form, or a report.

Today we'll export two different queries to two different formats:

  1. We'll export our Iron Maiden Albums query to an Excel spreadsheet.
  2. We'll export our Albums from the last 25 years query to an XML file.

Export a Query to Excel

Let's export the results of the Iron Maiden Albums query that we saved previously.

In this case, we'll export the query results to an Excel spreadsheet.

  1. Launch the Export Dialog

    Right-click on the query in the left menu (in our case, the Iron Maiden Albums query) and select Export > Excel from the contextual menu.

    Alternatively, you can select the query in the left menu, then click Excel from the export options in the Ribbon (on the External Data tab).

  2. Export Options

    Review the options. In our case, we'll keep the file name as Access has suggested. However, we'll put a tick next to Export data with formatting and layout.

    Click OK to export the data.

  3. Save Export Steps?

    Access gives you the option of saving the steps you just took, so that you can re-run them again later.

    In our case, we'll give it a miss. Click Close to finish.

  4. View the Exported File

    You can now check your exported file in Excel. Navigate to the location you chose at step 2 and open the file.

    You can save yourself from having to navigate to the file by checking Open the destination file after the export operation is complete at step 2.

Export to an XML File

Now we'll export our Albums from the last 25 Years query (that we built with the Expression Builder) to an XML file.

  1. Launch the Export Dialog

    Right-click on the query in the left menu (in our case, the Albums from the last 25 Years query) and select Export > XML File from the contextual menu.

    Alternatively, you can select the query in the left menu, then click XML File from the export options in the Ribbon (on the External Data tab).

  2. File Destination

    Review the file destination. In our case, we'll keep the file name and destination as Access has suggested.

    Click OK.

  3. Select Export Information

    Review/change the export information as required. In our case, we'll just keep the first two options checked. This will generate two files: the XML file and an XSD file (which will hold rules, such as the data types).

    Click OK to export the files.

  4. Save Export Steps?

    Access gives you the option of saving the steps you just took, so that you can re-run them again later.

    In our case, we'll give it a miss. Click Close to finish.

  5. Review the File/s

    Navigate to the location you chose for the files and open them.

Comments