Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Data Import, Export and Linking

Exporting Data from Access

Export Microsoft Access tables, queries and reports to Excel, CSV, PDF, Word and XML. Learn the Export wizard, saved exports, DoCmd methods and which format to pick.

Data in Access is rarely the end of the story: someone needs a spreadsheet, an accountant needs a CSV, a customer needs a PDF invoice, another system needs XML. Access can export tables, queries, forms and reports, and you can automate every export. The skill is mostly choosing the right source and the right format.

Export from a query, not a table

Export from a query whenever you can. A query lets you choose the fields, the order, the filter and friendly column names (SELECT Name AS [Customer], ...), so the recipient gets exactly the columns they need and none of the internal IDs. A saved query such as qryOrdersForAccounting can be exported again and again with the same shape, and when you fix the query everyone gets the fix.

TEXT
SELECT o.OrderID AS [Order No], c.Name AS Customer,
       Format(o.OrderDate, "yyyy-mm-dd") AS [Order Date],
       o.Total
FROM Orders AS o INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= #2025-01-01#
ORDER BY o.OrderDate;

Here is the same shape of export query running in SQLite so you can see the result a spreadsheet would receive:

SQL Stand-in: a query shaped for export

Press Run (or Ctrl + Enter) to see the result here.

The Export wizard

Select the object in the Navigation Pane, then use External Data > Export and pick a format:

Format Best for Notes
Excel analysis and sharing tables choose Export data with formatting and layout to keep captions and formats; unticked is faster
Text file / CSV exchanging with other systems choose delimiter, text qualifier and Include Field Names on First Row
PDF or XPS final reports and invoices pick quality: Standard (print) or Minimum (online)
Word (RTF) editable text layout is simplified
XML structured data exchange optional schema and presentation
HTML quick web tables basic styling
Another Access database copying objects between files also exports table structure only
ODBC sending a table to a server see linked tables and ODBC

Tick Save export steps to store the export under Saved Exports so you can repeat it later, even from an Outlook task on a schedule.

Export details that cause surprises

  • Commas inside values: a CSV needs the text qualifier (a double quote), otherwise Smith, John splits into two columns. Check Text Qualifier in Advanced.
  • Line breaks inside a text field also need the qualifier and still confuse some readers; replace them in the query with Replace([Notes], Chr(13) & Chr(10), " ").
  • Dates and decimal separators: exported CSV follows the machine's regional settings. For files other systems read, use Format([Date], "yyyy-mm-dd") and specify the decimal symbol in an export specification (Advanced > Save As).
  • Memo/Long Text can be truncated by older Excel exports (255 characters in .xls). Prefer .xlsx or export to CSV.
  • Leading zeros: Excel will happily convert 01234 to 1234 when opened. Export a text column as quoted text, or have the recipient import it as Text.
  • Large tables: Excel stops at about 1,048,576 rows per sheet. Export a filtered query or use CSV.

The CSV rules above are easy to see in action. The JavaScript stand-in below builds a CSV, quoting values that contain commas, quotes or line breaks, which is exactly what the text qualifier does:

JS Stand-in: why the text qualifier matters

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Exporting reports and invoices

Reports are what customers see, so export them as PDF. To produce one PDF per customer you cannot click the wizard fifty times, so use VBA:

VBA
Public Sub ExportInvoicePdf(lngOrderId As Long)
    Dim path As String
    path = Environ("USERPROFILE") & "\Documents\Invoices\Invoice-" & lngOrderId & ".pdf"
    DoCmd.OpenReport "rptInvoice", acViewPreview, , "OrderID = " & lngOrderId, acHidden
    DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, path
    DoCmd.Close acReport, "rptInvoice"
End Sub

OutputTo is the single call that exports to PDF, RTF, text or Excel (acFormatXLSX). Make sure the folder exists (MkDir, guarded by Dir) and that you pass the OrderID filter, so each file holds one invoice. The report must be opened first when you filter it with a Where condition.

Automating exports with DoCmd

VBA
' Query to a CSV file with an export specification (delimiter, date format)
DoCmd.TransferText acExportDelim, "AccountingSpec", "qryOrdersForAccounting", _
                   "C:\Exports\orders.csv", True

' Query to an Excel workbook, with field names
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                          "qryOrdersForAccounting", "C:\Exports\orders.xlsx", True

Wrap file operations in error handling: the target file might be open in Excel (a lock error), or the folder might not exist. After a successful export, open the file for the user with Application.FollowHyperlink path.

Which format when?

flowchart TD
  A["Who receives the data?"] --> B{"A person who reads it?"}
  B -->|yes, final document| C["PDF"]
  B -->|yes, will analyze| D["Excel .xlsx"]
  B -->|no, another system| E{"Structured with nesting?"}
  E -->|yes| F["XML"]
  E -->|flat rows| G["CSV with a spec"]

Pick the format from the recipient's needs, then build a query that has exactly the right columns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why export from a query instead of directly from a table?

    A saved query gives a repeatable, tailored export and hides internal columns.
  2. 2. What does the text qualifier do in a CSV export?

    Values containing the delimiter are enclosed in quotes, usually double quotes.
  3. 3. Which DoCmd method exports a report to PDF from VBA?

    OutputTo with acFormatPDF writes the report as a PDF file.
  4. 4. What is a Saved Export?

    Tick Save export steps in the wizard; run it later from External Data > Saved Exports.

Summary

  • Export from a saved query so columns, order and filters are exactly what the recipient needs.
  • Pick the format by audience: PDF for final documents, Excel for analysis, CSV or XML for systems.
  • Watch delimiters, text qualifiers, regional dates and decimals, leading zeros and Excel's row limit.
  • Automate with Saved Exports, TransferText, TransferSpreadsheet and OutputTo, with error handling.

Next: Linked tables and ODBC