Forms & Reports:
Export from Access

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

When a program cannot view Access database tables/queries directly, you can share data by exporting it from Access. You must choose another file format that both understand, like csv (comma separated values), which is a text format, or perhaps one of the dbf formats.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/Link Arrow: subtopic open
    Icon: StepImport Spreadsheet
    Icon: StepImport Text File
    Icon: StepImport Access Objects
    Icon: StepLink
    Icon: StepExport from Access
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    ExercisesTo subtopics


What You Can Export

  • A complete table with its data
  • Just the structure of a table
  • Results of a query, form, or report as a datasheet.
  • Database objects, including forms and reports, to another Access database.

Methods of Exporting

There are a number of ways to get data out of Access into a form that another program can use.

  • Make-Table query:
    Choose to create the table in another Access database instead of the current one. The database must already exist.
  • Menu:
    Select object to export.  File | Export...   Select the file type and file name of the destination file- another Access database, another kind of database, a spreadsheet, or a text format. A database must already exist before you can export to it.
    Exporting a form or report to another Access database just puts a copy of the design in the database. Exporting a form or report to a text or spreadsheet format will copy the data shown by the form or report.
    Using the command  Send To | Mail Recipient (as Attachment)  will also export your object as an attachment. You must have an email account like Outlook that can handle MAPI mail to use this feature.
  • Publish It with Word:
    Select the object in the Database Window or select part of a datasheet. From the menu select Tools |  Office Links | Publish It with Microsoft Word . A Word document is created and opened.

Tables and datasheets are often far too wide to fit onto the paper, so this command is not likely to be useful for a them. Forms and reports often export quite neatly to Word.

  • Analyze It with Excel:
    Select the object in the Database Window or select part of a datasheet. From the menu select Tools |  Office Links | Analyze It with Microsoft Excel . An Excel document is created and opened.
  • Copy and Paste:
    Select data in a datasheet. Copy. Switch to your destination file and paste.

Icon: Step-by-Step 

Step-by-Step: Export Table from Access

 Icon: Step-by-Step

What you will learn:

to export as text file
to export as spreadsheet file
to export to another Access database

Start with:  Class diskresource files, worldtravel.mdb

World Travel Inc. needs to share its Staff table with some of its offices that have not yet started using Access. The table needs to exported as a comma-delimited text file (csv) and also as an Excel spreadsheet (xls) since different offices have been using different software. (It is a BAD PLAN for different parts of a company to use different software, but is not unusual in the real world!)

Once you have other kinds of objects in Access, you can export them similarly.

Export Table as Text File

  1. If necessary, open the database worldtravel.mdb which you created earlier and have been modifying.
  2. Menu: File | ExportSelect the table Staff in the Database Window.
  3. From the menu select  File | Export .
    The Export dialog appears.
    Icon: TroubleProblem: The command Export is grayed out (not available)
    You did not select an object to export in the Database Window.
  4. Dialog: ExportNavigate, if necessary, to the folder databases project4 on your Class disk.
    The File name box should say "Staff" and the Title bar should read "Export Table 'Staff' As".
    Icon: TroubleProblem: File name is not Staff
    You did not select the Staff table in the Database Window.
    Solution: Close this dialog and start over.
  5. Change the "Save as type" box to Text Files.
  6. Click on the Export button.
    The Export Text Wizard appears. You will have to make several choices, like you did in the Import Text Wizard.
  7. Dialog: Export Text Wizard - step 1, DelimitedIn step 1, choose Delimited, which is the default choice..
  8. Click on Next.
  9. Dialog: Export Text Wizard - step 2, Comma, Field Names on First RowIn step 2, select Comma as the delimiter and check the box for Field Names on First Row.
  10. Click on Next.
  11. Dialog: Text Export Wizard - step 3, Verify pathIn step 3, verify the path to where the wizard is going to save the file. It should be on your Class disk in the folder for databases project4. (Your path may be different from the illustration.)
  12. Click on Finish.
     A message appears when the export is done.

    Message: Finished exporting

  13. Click on OK. The box closes and you are back in the Database Window.

Export as Spreadsheet

  1. In the Database Window, again select the table Staff.
    The Export dialog appears.
  2. Dialog: ExportIf necessary, navigate to your Class disk to the folder databases project4.
  3. Change the "Save as type" box to Microsoft Excel 97-2003 (*.xls).

    WarningKnow which software version will be used:
    Versions of Excel before 97 will not be able to open a file in the format you just chose.  Those are really old programs now, but there are many old programs still in use. When in doubt, ASK the users!
  4. Click on the Export button.
    This time no wizard is needed. Access quietly creates the file and puts you back in the Database Window.

Verify Files

It is ALWAYS a good idea to make sure that the file you exported will actually open the way you expect it to do! Of course you won't be able to verify if you do not have the correct software to open the exported data.

  1. My Computer window: TXT and XLS filesIn an Explorer window, navigate to your Class disk and open the folder databases project4 where you just saved the text and spreadsheet files.
    TipTemporary file: This folder will have 3 or 4 files. The file worldtravel.ldb is a temporary file that appears only while the database worldtravel.mdb is open.
  2. Staff as an Excel fileIf you have Excel installed on this computer, double-click on the file Staff.xls.
    The file opens in Excel. Scroll to make sure that all the columns were exported correctly.

  3. Close Excel.
  4. Right Click Menu: Open With... NotepadIn the Explorer window, right click on the file Staff.txt and choose  Open With | Notepad  from the popup menu.

    Notepad opens the text file

    Notepad opens the text file. It's hard to know if this is all correct, but at least we know that data was exported.

  5. Close Notepad.