You cannot export database diagrams to an Access project or an Access database. You cannot export queries or relationships from an Access database to an Access project.
When exporting data to an XML document, you can export the main table and any related tables. When exporting to another database, you can only export one object at a time. If you want to export multiple objects to a database, open the database you want to export to, and then import the objects instead.
When you export an object to another database, you might also want to export related objects to make it work. For example, a report might have a query as a record source, and that query might be based on a relationship between two tables.
When you export a data access page, you are exporting only the link to the corresponding HTML file. The HTML file remains in the same location, which means you have two data access pages pointing to the same corresponding HTML file. If you don't want this, save one of the data access pages under a different name and enter a different file location for the new corresponding HTML file.
You can use the Transfer Database wizard to create a new SQL Server database and export the objects from an existing Access database. See Help for more information on the Transfer Database wizard.
You can e-mail the output of a database object by attaching it to an electronic mail message in several different file formats, including Microsoft Excel (.xls), Rich Text Format (.rtf), MS-DOS text (.txt), HTML (.html), IDC/HTX, ASP, and data access pages (.html). You can also send a page as the body of an e-mail message.
To send a database object in e-mail, you need Microsoft Outlook, Microsoft Exchange, Microsoft Mail, or another electronic mail application that supports Messaging Application Programming Interface (MAPI).
Considerations when sending a data access page in e-mail.
When you send a data access page in e-mail, there are important connection and security considerations you need to be aware of to ensure more reliable results.
A data access page is connected to one of two types of data sources: a Microsoft Access database or a Microsoft SQL Server database. For the page to work properly when sent via e-mail, the source database must be on a shared server or shared computer in order for users to view and interact with the page.
Before you create the page, open the Microsoft Access database by using a universal naming convention (UNC) path in the File name box on the Open dialog box (File menu, Open command). This ensures that the data source of the page is defined with a UNC path instead of with the drive letter of a mapped network drive in Microsoft Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for the page to be located.
When you create the page, you should save it using a UNC path in the File name box of the Save As Data Access Page dialog box. Saving your page with a UNC path helps protect you when you send a pointer to the page by e-mail.
Store the database and the page on the same server.
To further help protect your pages, put the Microsoft Access database or Microsoft SQL Server database on the same Web server as the data access page.
Note You can't copy an Access database to or create an Access database in a folder under Network Places.
Publish from a trusted intranet security zone.
Depending on the Microsoft Internet Explorer security level setting on the recipient's computer, one of three things occurs when the recipient opens an e-mail message containing a data access page: the page is automatically disabled, the recipient is asked whether to allow data access, or the page is automatically enabled.
Note It is recommended that you set your Internet Explorer security setting for Access data sources across domains to Prompt.
If you are using a corporate intranet, you can publish your pages from a Web site located in a trusted security zone. This will help alleviate problems caused by the recipient's Internet Explorer security settings and thus improve performance of your pages. For more information about security levels and security zones, see Internet Explorer Help. For any specific page security requirements at your site, see your system administrator.
Send a pointer instead of a copy of the HTML source code.
There are three ways Microsoft Access can send a data access page in an e-mail message.
- In the body of the message, as a copy of the HTML source code of the page
- In the body of the message, as a pointer to the HTML file associated with the page
- As an attachment to the message, including a copy of the HTML file associated with the page
In the e-mail message, a copy of the HTML source code of a page looks very different than a pointer to the page. The copy will contain all of the HTML tags and objects needed to render the page. The pointer will contain some HTML tags and the absolute path
It's best to avoid sending a copy of the HTML source code of a page because Microsoft Outlook won't let a user connect to data across domains unless the recipient or system administrator has changed the default Internet Explorer security zone settings. Sending a pointer to the page assumes that the page and the data source that the page connects to are located on a server or public share. This reduces the chance for cross-domain issues within Microsoft Outlook.
Sending a pointer instead of a copy of the page can also be a security precaution that reduces the risk of malicious users intercepting a copy of the HTML source code of a page that has been sent to a user, and then revising it to covertly gain access to a corporate database.
The best way to send a data access page is to send a pointer to the page in the body of the e-mail message. To send a pointer, you can do one of the following:
- Save the page to a folder under Network Places or on a Web server, so the page's location is a URL.
- Save the page to a shared computer using a UNC path.
In either case, Access uses the <IFRAME> HTML tag to send a pointer to the page. The pointer is either the URL or the UNC path to the page.
When you create your e-mail message, Access provides confirmation dialog boxes to help you make the right choice.
To help maintain security when sending a page, first send a pointer to the page to yourself and then open the e-mail message. In the message window, right-click the page, and then click View Source. If the HTML text looks similar to the following code, then you are sending a pointer to the page (and not a copy of the HTML source code):
<HTML><BODY STYLE="OVERFLOW:HIDDEN" BOTTOMMARGIN=0
LEFTMARGIN=0 TOPMARGIN=0 RIGHTMARGIN=0>
<IFRAME FRAMEBORDER=0 HEIGHT=100% WIDTH=100%
SRC="http://NWWeb/Northwind pages/View Products.htm"></IFRAME>
</BODY><HTML>
There are four ways you can use Microsoft Access data or database objects to create a Web page:
- In a Web browser that supports Dynamic HTML version 4.0 or later, you can use data access pages. You do not output data access pages; you create a data access page as a database object that is actually a link to the data access page's corresponding HTML file. Use data access pages to view, edit, update, delete, filter, group, and sort live data from either a Microsoft Access database or a Microsoft SQL Server database.
- You can output server-generated HTML files, either ASP or IDC/HTX, from tables, queries, and forms. Server-generated HTML files are displayed in a table format in a Web browser. Use server-generated HTML files when you want to use any Web browser, your data changes frequently, or you need to see live data in a table connected to an ODBC data source, but you don't need to update or interact with the data.
- You can output static HTML files from tables, queries, forms, and reports. In a Web browser, reports display in a report format, and tables, queries, and forms display in a datasheet format. Use static HTML files when you want to use any Web browser that supports HTML version 3.2 or later and your data does not change frequently.
- You can save a form or report as a data access page by using the Save As option under the File menu. This allows you to use the form or report as Web page.
Exporting to or exchanging data with Microsoft Word or another word-processing program.
There are three ways to use your Microsoft Access data with word-processing programs:
- In an Access database, you can use the Microsoft Word Mail Merge Wizard to create a mail merge document in Word that links to Access data. Once the link is established, you can open your document in Word at any time to print a new batch of form letters or labels using the current data in Access.
- For any version of Microsoft Word, you can export Microsoft Access data to a mail merge data source file that can be used with the mail merge feature of Word.
- You can save the output of a datasheet, form, or report as a Rich Text Format (.rtf) file or a text (.txt) file. A Rich Text Format file preserves formatting such as fonts and styles, and can be opened with Microsoft Word and other Microsoft Windows word-processing or desktop-publishing programs.
Exporting to Microsoft Excel or another spreadsheet program.
There are three ways you can use Microsoft Access data with Microsoft Excel or another spreadsheet program:
- You can export a datasheet as unformatted data to Microsoft Excel or another spreadsheet program.
- You can save the output of a datasheet, form, or report directly as an Excel (.xls) file or worksheet. This way, you can preserve most formatting, such as fonts and colors. Report group levels are saved as Excel outline levels, and a form is saved as a table of data.
- You can also export the data from a table, query/view, datasheet, form, or report to an XML file which can then be imported into Excel.
Exporting or copying a PivotTable or PivotChart view.
If you are familiar with Microsoft Excel PivotTable reports and want to work with the data in Excel, there are two ways to accomplish this.
Exporting data to an interactive Excel PivotTable report.
To view and modify the contents of a PivotTable view by using Excel, you can export the PivotTable view to Excel.
Preparing for export to Excel Excel PivotTable reports cannot display detail fields. When you export to Excel, detail fields will be available on the PivotTable toolbar in Excel, but the fields won't be displayed in the report. If you want the Excel PivotTable report to reflect the appearance of the PivotTable view, before you export to an Excel PivotTable report, either move all the fields out of the detail area, or hide detail data for items and cells so that the detail area is not displayed.
After you export You might notice some differences in your PivotTable view after you export it to Excel. This is because PivotTable views use the Microsoft Office PivotTable Component, and Excel PivotTable reports either do not support certain PivotTable list features, or they implement some features differently. For information about differences between PivotTable lists and Excel PivotTable reports, see Excel Help.
Copying data to another program for noninteractive use.
Copying to Microsoft Word or Excel When you want to work with a snapshot of the data currently displayed in a PivotTable view, you can copy and paste the data into Excel or Word. When you copy the whole PivotTable view to another program, or copy selected parts of the data, the text and values from the PivotTable view become part of your Word document or Excel workbook. No connection is created between the original source data and the copy in the document or workbook, so you won't be able to refresh or update the data that was copied.
Copying to other programs You can also copy data displayed in a PivotTable view to other programs, such as Microsoft FrontPage. When you copy data to a program other than Excel or Word that does not support the HTML format used by PivotTable views, the data is copied as unformatted text.
Exporting to Microsoft Windows SharePoint Services.
When you export data to XML, you can export just the data, the schema, or both. You can also choose to export a presentation XSL file. The file is generated based on presentation information in the Access form, report, or datasheet.
You can export static data in tables and queries, and forms and reports that have tables or queries as their data sources to XML. You can specify a transformation file and apply the transform following the export operation. You can export related tables and queries, and carry over any filter or sort order that is specified on the exported object to the output.
You can also export live data in tables, queries, stored procedures, functions, forms and reports in Microsoft Access Projects (ADPs) to XML. These database objects have SQL server as a live data source. You can specify the names of the SQL server and the database as the virtual directory, and export these database objects to an XML file.
To make frequent export operations more convenient, you can automate them by creating a macro or creating a Microsoft Visual Basic for Applications (VBA) procedure. This is useful, for example, when you export data on a regular schedule or you are building a Web site by using multiple database objects.
About import/export specifications and schema.ini files.
An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export specification is stored with the default name: Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.
You create an import/export specification by using either the Import Text Wizard or the Export Text Wizard. Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.
Alternatively, you can use a Schema.ini file in a Microsoft Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Microsoft Windows registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.