Export data or database objects

Microsoft Office Access 2003

previous page next page
  1. In the Database window, click the name of the object you want to export, and then on the File menu, click Export.
  2. Click the arrow to the right of the Save in box, and select the drive or folder where the database you want to export to is located.
  3. Double-click the icon for the database that you want to export to.
  4. In the Export dialog box, enter a name for the new object (or accept the current name). If you are exporting a table, in Export dialog box, select whether you want to export both the table's definition and data or just the table's definition.

ShowTo a Microsoft Excel or other spreadsheet

Note  In addition to the following methods, you can also export Access data to an Extensible Markup Language (XML) file which can then be imported into Microsoft Excel.

ShowSave an object's output as a Microsoft Excel file

  1. In the Database window, click the name of the object you want to save. To save part of a datasheet, open the datasheet and select a portion of the datasheet before continuing.

  2. On the File menu, click Export.

  3. In the Save as type box, click Microsoft Excel 5-7 or Microsoft Excel 97-2003.

  4. Click the arrow to the right of the Save in box, and select the drive or folder to save to.

  5. In the File name box, enter a name for the file (or use the suggested name).

  6. Select the Save formatted check box.

  7. Do one of the following:

    • If you are saving a datasheet, click Export All to save the entire datasheet or Save Selection if you selected a portion of the datasheet in step 1.

    • For all other database objects, click Export.

ShowLoad the output of a table, query, form, or report into Microsoft Excel

  1. In the Database window, click the name of the datasheet, form, or report you want to save and load into Microsoft Excel. To save a selection of a datasheet, open the datasheet, and then select the portion of the datasheet before continuing.

  2. On the Tools menu, point to Office Links, and then click Analyze It with Microsoft Excel.

ShowExport or copy a PivotTable view

  1. Open a datasheet or form in PivotTable view.
  2. Do one of the following:

ShowExport a PivotTable view to Excel

Use this procedure to export a PivotTable view of a datasheet or form to Microsoft Excel as an interactive PivotTable report. The link between the Excel PivotTable report and the source data is maintained.

For best results, either move all fields out of the detail area or hide detail data for items before you export the PivotTable view to Excel.

  • On the PivotTable toolbar, click Export to Microsoft Excel Button image to run or switch to Excel and display the data in a PivotTable report.

Note  Because PivotTable reports in Excel cannot display some PivotTable view layouts and calculations, the PivotTable report might look different from the PivotTable view. For information about working with Excel PivotTable reports and adding fields to them, see Excel Help.

ShowCopy a PivotTable view to Excel or Word

Use this procedure to copy a PivotTable view, or a part of it, as a noninteractive, formatted table in Microsoft Excel or Microsoft Word.

  1. Do one of the following:

    • To copy the entire PivotTable view, press CTRL+A.

    • To copy a part of a PivotTable view, select the elements that you want to copy.

  2. On the PivotTable toolbar, click Copy Button image.

  3. Switch to Excel or Word, and click where you want the data from the PivotTable view to appear.

  4. Click Paste Button image.

Note  When you copy a PivotTable view, or a part of it, you are copying only the formatting and data values. No link is maintained between the copy in Excel or Word and the source data. The copy in Word or Excel is noninteractive and cannot be refreshed.

ShowExport a spreadsheet on a data access page to a Microsoft Excel workbook

  1. In the design window, make sure the spreadsheet is activated by double-clicking on the spreadsheet. This will open the application that created the object, which in this case is Microsoft Excel. For additional information on activating a spreadsheet, see Excel Help.
  2. On the toolbar in the spreadsheet, click Export to Microsoft Excel Button image to run or switch to Excel and display the data in a workbook.

Notes

  • Because Excel only supports 256 columns and 65,536 rows, data in columns IW through ZZZ and rows 65,537 through 262,144 are not exported from the spreadsheet. You can, however, copy this data to more than one Excel worksheet.
  • When you export data from a spreadsheet to Excel, only the values and formulas are exported. Links that connect to other data on the Web page or to other Web pages are not retained in Excel.
  • When you export some spreadsheet number formats, the data in Excel is displayed with a custom number format. For example, the Yes/no, True/false, and On/off spreadsheet formats are displayed with custom number formats in Excel. For information about custom number formats in Excel, see Excel Help.

To a Web page

ShowExport to HTML format

Depending on whether you need static or dynamic data, you can export to different types of HTML data in Microsoft Access.

ShowExport a datasheet to dynamic HTML format

  1. In the Database window, click the name of the table, query, or form you want to export, and then on the File menu, click Export.
  2. In the Save As Type box, click Microsoft IIS 1-2 (*.htx; *.idc) or Microsoft Active Server Pages (*.asp).
  3. Click the arrow to the right of the Save In box and select the drive or folder to export to.
  4. In the File Name box, enter the file name.
  5. Click Export.
  6. Enter the appropriate information in the HTX/IDC Output Options or Microsoft Active Server Pages Output Options dialog boxes:

    • In the HTML Template box, enter the location of an HTML template.

    • In the Data Source Name box, enter the name of the ODBC data source that you will connect to when the server-generated HTML files are processed on the Web server.

  7. Do one of the following:
    • For a Microsoft Access database (.mdb)

      In the User to Connect As and Password for User boxes, enter a user-level security username and password to allow users to access the database from the Web page. If you do not enter a username or password, the default username is Admin, and no password is used.

      Warning  This will allow anyone with default Administrator privileges to access your database from the Web page. It is highly recommended that Administrator accounts have strong passwords assigned to them. Use strong passwords that combine upper- and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

    • For a Microsoft Access project (.adp)

      In the Username and Password boxes, enter a database username and password to allow users to access the Microsoft SQL Server database from the Web page. If you do not enter a username or password, the default username is Sa and no password is used.

      Warning  This will allow anyone with default Administrator privileges to access your database from the Web page. It is highly recommended that Administrator accounts have strong passwords assigned to them. Use strong passwords that combine upper- and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

    Warning  The password you provide is displayed in the dialog box and stored in the file as plain text. An unauthorized user may be able to access the information in the file and compromise the security of the data source. As an alternative, you can use ODBC Data Source Administrator to add a system data source name (DSN) to your computer and configure that DSN with a user name and a password. You can then specify the system DSN as the Data Source Name in the Microsoft Active Server Pages Output Options dialog box and leave the Username and Password fields blank. For more information about ODBC Data Source Administrator, see Microsoft Windows Help.

  8. Once you have exported a file to ASP or IDC/HTX format, you need to publish the files to make them available on the World Wide Web.

ShowExport a datasheet to static HTML format

  1. In the Database window, click the name of the table, query, or form, you want to export, and then on the File menu, click Export.
  2. In the Save As Type box, click HTML Documents (*.html;*.htm).
  3. Click the arrow to the right of the Save In box and select the drive or folder to export to.
  4. In the File Name box, enter the file name.
  5. Select the Save Formatted check box to:
    • save the table, query, or form in a format similar to its appearance in Datasheet view,
    • enable the AutoStart check box,
    • and display the HTML Output Options dialog box after you click Save in step 7.
  6. Select AutoStart if you want to display the results in your default Web browser.
  7. Click Save.
  8. If you selected Save Formatted in step 5, the HTML Output Options dialog box is displayed. In the HTML Template box, you can enter the location of an HTML template. You can also set encoding options (Default encoding, Unicode, and Unicode (UTF-8)) for saving the file.

  9. Once you have output a datasheet to an HTML file, you need to publish it to make it available on the World Wide Web.

Notes

  • If the datasheet contains a parameter query, Microsoft Access first prompts you for the parameter values, then exports the results.
  • Fields with a Hyperlink data type are exported as hyperlink addresses by using <A HREF> HTML tags.
  • Although a default theme is not applied when you export the datasheet, you can apply a theme by creating a data access page from an existing Web page (in this case, the static HTML file you just created), applying a theme in page Design view, and then saving the page.
  • A datasheet with many records may take a long time to export and display through a Web browser. Consider reducing the size of the datasheet by using criteria such as a date field, or by using a parameter query.

ShowExport a report to static HTML format

  1. In the Database window, click the name of the report you want to export, and then on the File menu, click Export.
  2. In the Save As Type box, click HTML Documents (*.html; *.htm).
  3. Click the arrow to the right of the Save In box and select the drive or folder to export to.
  4. In the File Name box, enter the file name.
  5. Select the AutoStart check box to display the results in your default Web browser.
  6. Click Save.

  7. In the HTML Template box of the HTML Output Options dialog box, enter the location of an HTML template. If you do not specify an HTML template file containing navigation tokens, Microsoft Access provides a default navigation scheme.

  8. Once you have exported a report to an HTML file, you need to publish it to make it available on the World Wide Web.

Notes

  • Access exports a report as multiple HTML files, with one file per printed page. The file names are created by using the object name and a suffix; for example, Products.html, ProductsPage2.html, ProductPage3.html, and so on.
  • If the report contains a parameter query, Access first prompts you for the parameter values, then exports the results.
  • Fields with a Hyperlink data type are exported as hyperlink addresses by using <A HREF> HTML tags.
  • Controls and features of a report, including subreports, are supported except for lines, rectangles, and OLE objects. However, you can use an HTML template file to include images in a report header or report footer.
  • Although a default theme is not applied when you export the report, you can apply a theme by creating a data access page from an existing Web page (in this case, the static HTML file you just created), applying a theme in page Design view, and then saving the page.
  • Access provides several ways to display report information in a Web browser in addition to static HTML reports. You can display a report snapshot on a Web page, and you can create a grouped data access page. However, you cannot export a report to a server-generated HTML format.

ShowExport to XML format

When you export to XML format, you can also export the presentation stylesheet (XSL) and create a Web page that displays the XML data formatted according to the stylesheet.

ShowExport a table, query, form, or report to XML data with the presentation stylesheet

  1. In the Database window, click the name of the table, query, form, or report that you want to export, and then on the File menu, click Export.
  2. In the Save as type box, click XML(*.xml).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, enter a name for the file, and then click Export.
  5. In the Export XML dialog box, check the selections to export the data and the presentation style sheet.
  6. Click More options to set further options to export static or live data and the presentation style sheet.

To Microsoft Windows SharePoint Services

ShowExport a table or query to a Windows SharePoint Services list

  1. In the Database window, click the name of the table or query you want to export, and then on the File menu, click Export.
  2. In the Save as type box, click Windows SharePoint Services().
  3. This opens the Export to Windows SharePoint Services Wizard. Follow the directions in the wizard to complete the export.

To Microsoft Word or other text format

ShowExport a datasheet to a delimited or fixed-width text file

  1. In the Database window, click the name of the table, query, view, or stored procedure you want to export, and then on the File menu, click Export.
  2. In the Save as type box, click Text Files (*.txt; *.csv; *.tab; *asc).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File Name box, enter a name for the file (or use the suggested name), and then click Export.

    Microsoft Access starts the Export Text Wizard.

  5. Follow the directions in the dialog boxes. Click Advanced to create or use an import/export specification.

ShowSave an object's output as a Rich Text Format file

  1. In the Database window, click the name of the object you want to save. To save a selection of a datasheet, open the datasheet and select the portion of the datasheet before continuing.
  2. On the File menu, click Export.
  3. In the Save as type box, click Rich Text Format (*.rtf).
  4. Click the arrow to the right of the Save in box, and select the drive or folder to save to.
  5. In the File name box, enter a name for the file (or use the suggested name).
  6. Do one of the following:
    • If you are saving a datasheet, click Export All to save the entire datasheet or click the arrow to the right of the Export All box, and select Save Selection if you selected a portion of the datasheet in step 1.
    • For all other database objects, click Export.

ShowLoad the output of a table, query, form, or report into Microsoft Word

  1. In the Database window, click the name of the datasheet, form, or report you want to save and load into Microsoft Word. To save a selection of a datasheet, open the datasheet and select the portion of the datasheet before continuing.
  2. On the Tools menu, point to Office Links, and click Publish It with Microsoft Word.

ShowSave a datasheet to an encoded format

  1. On the File menu, click Export.
  2. In the Save as type box, click Text Files (*.txt; *.csv; *.tab; *asc).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, enter a name for the file (or use the suggested name).
  5. Select the Save formatted check box.
  6. In the Encode <objectname> As dialog box, select the method of encoding as Windows (default), MS-DOS, Unicode, or Unicode (UTF-8).

To XML

ShowExport to XML

In Microsoft Access, you can output data to Extensible Markup Language (XML) by exporting to an XML document. You can also export a schema to specify the structure of the XML document as well as a stylesheet to describe the presentation of the data.

  1. In the Database window, click the name of the table, query, form, or report that you want to export, and then on the File menu, click Export.
  2. In the Save as type box, click XML(*.xml).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, enter a name for the file, and then click Export.
  5. In the Export XML dialog box, choose to export one or more of the following: the data, the schema, and the presentation style sheet.
  6. Click More options to set further options to export static or live data, the schema and the presentation style sheet.

To another database

ShowExport a table or query to a Paradox or dBASE file

Note  Microsoft has created updated dBASE and Paradox ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to dBASE and Paradox files. The default Jet 4.0-based dBase ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to dBASE and Paradox files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support and request that they be sent to you.

  1. In the Database window, click the name of the table or query (Microsoft Access database only) you want to export, and then on the File menu, click Export.
  2. In the Save as type box, click the Paradox or DBASE database format you want.
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, enter a name for the file (or use the suggested name), and then click Export.

ShowExport a table or query to an SQL database table or other ODBC data source

Note  You can only export a table or query to an SQL database or other ODBC data source if you have installed the appropriate driver.

  1. In the Database window, click the name of the table or query (Microsoft Access database only) you want to export, and then on the File menu, click Export.
  2. In the Export Object Type 'Object name' To dialog box, in the Save As Type box, click ODBC Databases ().
  3. In the Export dialog box, enter a name for the file (or use the suggested name), and then click OK.

    The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer.

  4. Click either the File Data Source or Machine Data Source tab, and then double-click the ODBC data source that you want to export to.

    To define a new data source for any installed ODBC driver, click New, and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before continuing.

    Particular ODBC data sources have different requirements for accessing their data. For example, most require you to enter a logon ID and password. You might need to get this information from your system administrator. Enter the appropriate information in the dialog box, and then click OK.

Microsoft Access connects to the ODBC data source and creates the new table.

Note  After you export a table from a Microsoft Access database to an SQL database, you can use an SQL pass-through query to add an index to the table.

previous page start next page