Importing and Exporting Data in Microsoft Access

Data rarely lives in just one place. You receive spreadsheets from colleagues, export reports for management, and pull in data from external systems. Microsoft Access has robust tools for moving data in and out — once you know where to find them and how they work, you will handle data transfers with confidence.

The External Data Tab

All import and export operations in Access live on the External Data tab of the ribbon. This tab is divided into two groups: Import & Link on the left and Export on the right.

Importing Data into Access

Importing from Excel

Excel is the most common data source for Access imports. Here is the complete process:

1. Click External Data → New Data Source → From File → Excel
2. Browse to your Excel file and select it
3. Choose one of three options:
   • Import the source data into a new table — creates a brand new table
   • Append a copy of the records to the table — adds rows to an existing table
   • Link to the data source — creates a linked table (more on this below)
4. Select the worksheet or named range to import
5. Check "First Row Contains Column Headings" if applicable
6. Review and adjust field data types on the next screen
7. Choose or create a primary key
8. Name the new table and click Finish

Pro tip: Access will guess at data types based on the first few rows. Always review these guesses — a column of numbers that occasionally contains text will be imported as Text, not Number, unless you correct it.

Importing from CSV or Text Files

The process for CSV files is nearly identical to Excel:

1. Click External Data → New Data Source → From File → Text File
2. Browse to your CSV file
3. Choose Delimited (for CSV) or Fixed Width
4. Select the delimiter (comma, tab, semicolon, etc.)
5. Review field names and data types
6. Name the table and finish

Importing from Another Access Database

You can import tables, queries, forms, reports, macros, and modules from another Access database:

1. Click External Data → New Data Source → From Database → Access
2. Browse to the source database
3. Select the objects you want to import
4. Click OK

This is extremely useful for copying a well-designed form or report from one database to another.

Linked Tables vs. Imported Tables

When you import data, Access makes a copy. Changes to the original source file do not affect your Access table, and changes to your Access table do not affect the source.

When you link to data, Access creates a pointer to the original file. The data stays in the source file, and Access reads it live every time you open the linked table. Changes in the source are immediately visible in Access.

Use linked tables when:

• The source data is maintained by someone else and changes frequently
• You want to query Excel data without duplicating it
• You are connecting to a SQL Server backend

Use imported tables when:

• You want a snapshot of the data at a point in time
• You need to modify the data independently of the source
• Performance is a concern (linked tables are slower)

Exporting Data from Access

Exporting to Excel

1. In the Navigation Pane, select the table or query you want to export
2. Click External Data → Export → Excel
3. Choose a file name and location
4. Check "Export data with formatting and layout" to preserve column widths and formatting
5. Check "Open the destination file after the export operation is complete" to see the result immediately
6. Click OK

Exporting to CSV

1. Select the table or query
2. Click External Data → Export → Text File
3. Choose Delimited format
4. Select comma as the delimiter
5. Check "Include Field Names on First Row"
6. Choose a file name and click Finish

Exporting to PDF

Access can export any report directly to PDF:

1. Open the report in Report View or Print Preview
2. Click External Data → Export → PDF or XPS
3. Choose a file name and click Publish

This is one of the most useful export features — you can generate a perfectly formatted PDF report from your Access data with two clicks.

Saving Import and Export Specifications

If you perform the same import or export regularly, you can save the steps as a specification and reuse them:

1. At the end of any import or export wizard, click Save Import Steps or Save Export Steps
2. Give the specification a name
3. Optionally check Create an Outlook Task to schedule it

To run a saved specification later, click External Data → Saved Imports or Saved Exports and select the one you want.

You can also run saved specifications from a macro using the RunSavedImportExport action, which makes it easy to automate regular data refreshes with a button click.

Common Import Problems and Solutions

"Numeric Field Overflow" — A number in your source data is too large for the field type Access chose. Open the table in Design View and increase the field size.

"Type Conversion Failure" — Access could not convert a value to the expected data type. Check for text in numeric columns or invalid date formats.

Dates importing as text — Access is picky about date formats. Ensure your source dates are in a format Access recognizes (MM/DD/YYYY for US locale) before importing.

Blank rows at the end — Excel files often have phantom formatting in empty rows below your data. Delete all rows below your last data row in Excel before importing.

Conclusion

Access's import and export tools are more capable than most users realize. Whether you are pulling in a monthly data dump from a colleague's spreadsheet, linking to a live SQL Server table, or generating PDF reports on demand, the External Data tab has you covered. Master these workflows and you will spend far less time manually copying and pasting data between applications.