In This Chapter
Overview
Sharing Information with Microsoft Access 97
Sharing Information with Microsoft Excel
Sharing Information with Outlook 97
Sharing Information with PowerPoint
Sharing Information with Word
Using Microsoft Bookshelf in a Workgroup
Using Microsoft Camcorder in a Workgroup
Sharing Databases with Microsoft Office Macintosh Edition

See Also

• For information about using Office with an intranet, see Chapter 24, "Integrating Microsoft Office with Your Intranet."

• For information about integrating Office with messaging systems, see Chapter 28, "Working with Messaging Systems and Connectivity Software."

The Microsoft Office 97 applications support the standard means of exchanging data between applications, such as copying and pasting. In addition, each Office application includes application­specific support for importing and exporting data. This chapter describes these unique features in each Office application.

All Office applications support the following means of copying or linking data:

• Using the Cut, Copy, and Paste commands (Edit menu) to paste or link data.

• Using the Publishing submenu commands (Edit Menu) to create publishers and subscribers (Macintosh only).

• Using ActiveX and OLE controls based on the Component Object Model (COM).

• Creating hyperlinks between Microsoft Excel, Microsoft PowerPoint, and Microsoft Word documents, as well as hyperlinks to HTML documents.

  For more information about hyperlinks, see "Creating Hyperlinks in Office Documents" in Chapter 24, "Integrating Microsoft Office with Your Intranet."

In addition, each Office application has capabilities for sharing information with other applications. These capabilities are optimized for the purpose of the application and are described in the following sections.

Microsoft Access 97 fully supports copying and pasting linked or unlinked data, as described in the "Overview" earlier in this chapter. In addition, Access provides a variety of ways to exchange data with other applications and file formats.

Using External Data with Microsoft Access

Access is extremely flexible in its ability to handle data from a variety of sources. External data falls into two categories: Indexed Sequential Access Method (ISAM) and Open Database Connectivity (ODBC). The ISAM data sources are the traditional desktop PC­based database formats. These include FoxPro, Paradox, Access, and others.

The ODBC data sources are typically Structured Query Language (SQL) server tables, such as SQL Server tables; however, Access can use other ODBC data sources, as long as a 32­bit Level 1­compliant ODBC driver is installed for that data source.

Importing or Linking Data from External Data Sources

Access can either import or link external data. Importing reads the external data and creates a new table in the current Access database. The original data remains unchanged. Linking leaves the external data in its current location and format, and stores a link to that data in the current Access database.

When a table from another database is linked, it performs like a native, local table. Users can create queries, forms, and reports that use the external data; combine the external data with the data in Access tables; and even view and edit the external data while others are using it in the original application. Use this approach as an alternative to importing if the external data you want to use is also being updated by a database application other than Access.

Even if all the data you want to use is in Access format, you might find it advantageous to link to external data. By splitting the application (forms, reports, queries, macros, modules, and temporary tables) from the rest of the data, you can ease the support burden of distributing your application. Additionally, network traffic is reduced because forms, reports, and queries are run locally on each workstation instead of across the network.

There are two methods of importing or linking external data:

• Using commands on the Get External Data submenu (File menu)

• Using Visual Basic for Applications code or macros

Access can import or link data from any of the following external data sources:

• Databases created by applications that use the Jet database engine (Access, Excel, Visual Basic, and Visual C++)

• ODBC databases such as SQL Server version 4.2 or later

• Paradox versions 3.x, 4.x, and 5.0

• FoxPro version 2.x (and FoxPro version 3.0, import only)

• dBASE III, dBASE III+, dBASE IV, and dBASE 5

• Lotus WKS, WK1, WK3, and WK4 spreadsheets

• Excel versions 3.0, 4.0, 5.0, 95, and 97 worksheets

• Delimited text files

• Fixed­width text files

• HTML lists and tables

• Microsoft Exchange Client and Microsoft Outlook data, collectively referred to as Microsoft Windows Messaging Service data. (Linking data in this format is read­only.)

To import or link external data, applications that use the Jet database engine use one of several installable ISAM drivers or the SQL Server driver. To install most drivers for the external database you want to use, rerun Setup and click Add/Remove. Under the Data Access option, select the appropriate drivers.

The Paradox, Lotus 1­2­3, and Microsoft Messaging drivers are not installed during Setup. To install these drivers, use the Dataacc.exe program, which is included in the Office 97 Value Pack.

Note   The CD­ROM version of Office includes the Office 97 Value Pack, a collection of application extras such as clip art, maps, sounds, presentation enhancements, and utilities. For more information about the Value Pack and how to use its contents, see Valupack.hlp in the ValuPack folder on the Office CD. If you have Web access, you can also point to Microsoft on the Web (Help menu) in any Office application and then click Free Stuff.

The Setup programs for Office 97 Professional Edition and Access supply only one ODBC driver: the SQL Server driver. To use ODBC data from another source, you must install a 32­bit version of the appropriate ODBC driver from the vendor of that data format.

To import and link Microsoft Windows Messaging Service data you must install the Import Exchange/Outlook and Link Exchange/Outlook wizards that are available on the World Wide Web. For more information about importing and linking Microsoft Windows Messaging Service data, see "Using Microsoft Windows Messaging Service Data with Microsoft Access" later in this chapter.

Network Access Requirements

To link or directly open an external table on a network, you must be able to connect to the network and have access to the following components:

• The server and share (if applicable) on which the external database is located

  Server and share access are established through network permissions. For information about setting up network permissions, see your network product documentation.

• The external table

  Table access permissions, if they exist, are established using the security features of the external database. Depending on how security has been defined, you might need the appropriate passwords, or you might need to have a network administrator grant you the appropriate permissions to use the external table. For information about setting up access permissions, see your external database product documentation.

When specifying the database name for a database on a network drive, you can do one of the following:

• Specify the network path using the universal naming convention (UNC) format (server\share\path),
  if your network supports it.

• Establish a connection to the network drive first, and then specify the path using the network drive letter.

Performance Guidelines

Although you can use external tables just as you use Access tables, it is important to keep in mind that they are not actually in your Access database. As a result, each time you view data in an external table, Access retrieves records from another file. Performance is optimal if you link tables instead of opening them directly and if you retrieve and view only the data you need. For example, it is a good idea to use restrictive queries to limit the number of returned records, so you do not have to scroll unnecessarily.

For more information about improving performance, see "Optimizing Microsoft Access" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Case Sensitivity

Unlike searches on databases that use the Jet database engine, searches of external databases may be case­sensitive. However, searches are not case­sensitive in the following instances:

• For Paradox data, if the CollatingSequence entry in the Jet\3.5\Engines\Paradox key of the Windows registry is set to International, Norwegian­Danish, or Swedish­Finnish

• For FoxPro or dBASE data, if the CollatingSequence entry in the Jet\3.5\Engines\Xbase key of the Windows registry is set to International

• For ODBC data, if the server is configured not to be case­sensitive

In addition, if a search is made across more than one data source type, the case sensitivity depends on the collating sequences of the databases in which the query is stored.

Unsupported Objects and Methods

With the use of Visual Basic code, some DAOs and methods are intended for use only on databases created using the Jet database engine, such as databases created by Access, Visual Basic, and Excel. These DAOs and methods are not supported for use with external databases in other formats.

Unsupported DAOs include:

• Container

• Document

• QueryDef

• Relation

Unsupported methods include:

• CompactDatabase

• CreateDatabase

• CreateField (if the table has existing rows)

• CreateQueryDef

• RepairDatabase

In addition, the following transaction processing methods are supported only if the external database supports transactions:

• BeginTrans

• CommitTrans

• Rollback

Troubleshooting

The following information addresses problems you may encounter when importing from or linking to an external data source.

Connection Problems

If you have trouble connecting to an external data source, check your connection to the network. Make sure you have permission to use:

• The server and share (if applicable) on which the external database is located.

• The external table.

Other things to check include the following:

• Can you connect using another product or a different user account and password?

• Have you exceeded the connection limits on the server?

• Does the server have enough space?

• Does the connection information match the case sensitivity of the server?

If you have checked all these items and you still cannot connect, contact your external database vendor.

Temporary Space

When you query a database, Access creates temporary indexes on your hard disk, even if the database is on an external network device. Temporary space requirements can vary from a few thousand bytes to several megabytes, depending on the size of the external tables being queried.

Temporary space is allocated from the folder indicated by the TEMP environment variable, typically the Windows\Temp folder. If your system has not defined a TEMP environment variable, the current Access folder is used. If the TEMP environment variable points to an invalid path or if your hard disk does not have sufficient space for these temporary indexes, your application may perform unpredictably if Windows and Access run out of resources.

Exporting Data to Other File Formats

Access can export data to any of the following file formats:

• ODBC databases such as SQL Server 4.2 or later

• Paradox 3.x, 4.x, and 5.0

• FoxPro 2.x (and FoxPro 3.0, import only)

• dBASE III, dBASE III+, dBASE IV, and dBASE 5

• Lotus WK1 and WK3 spreadsheets

• Excel 3.0, 4.0, 5.0, 95, and 97 worksheets

• Delimited text files

• Fixed­width text files

• HTML lists and tables, for table, query, and form datasheets

• HTML documents, for reports

• Internet Database Connector (IDC) files for table, query, and form datasheets

• Active Server Pages (ASP) files for forms and for table, query, and form datasheets

• Word Merge files

• Rich Text Format (RTF) files

Before you can export to some of these formats, you may need to rerun Setup to install one of several ISAM drivers or the SQL Server driver. For more information, see "Importing or Linking Data from External Data Sources" earlier in this chapter.

After the appropriate drivers are installed, there are two methods of exporting to other file formats: using the Save As/Export command (File menu), or using the OutputTo method either in Visual Basic code or in macros. For more information about saving in these file formats, see Access online Help.

Using Microsoft Access Data in Word

There are four ways you can use Access data with Word. You can:

• Export Access data to a mail merge data source file that can be used with the mail merge feature of any version of Word.

• Save the output of a datasheet, form, or report as an RTF file. An RTF file preserves formatting, such as fonts and styles, and can be opened in Word and other Windows word­processing or desktop­publishing programs.

• Use the Word Mail Merge Wizard.

• Open the output of an Access datasheet, form, or report in Word automatically.

The first two options are available through the Save As/Export command (File menu). The last two options are described in the following sections.

Using the Word Mail Merge Wizard

If you are using Word 95 or later, you can use the Word Mail Merge Wizard to create a mail merge document in Word using a link 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.

To merge data from a table or query using the Word Mail Merge Wizard

1. In the Access Database window, click the name of the table or query you want to export.

2. On the Tools menu, point to OfficeLinks and click Merge It With MS Word.

3. Follow the instructions in the Mail Merge Wizard.

4. In the Word window, click Insert Merge Field to insert the desired fields into the document.

Note   You can also begin the operation of merging data into form letters from within Word. For more information, see Word online Help.

Opening Microsoft Access Output in Word Automatically

You can save the output of a datasheet, form, or report as an RTF file and automatically open the file in Word.

To open the output of a datasheet, form, or report in Word

1. In the Access Database window, click the name of the table, query, form, or report you want to save and open in Word.

   – or –

   To save a portion of a datasheet, open the datasheet and select the portion of the datasheet.

2. On the Tools menu, point to OfficeLinks, and click Publish It With MS Word.

   The output is saved as an RTF file in the folder where Access is installed. Word automatically starts and opens the RTF file.

Exchanging Data with Microsoft Excel

Access and Excel provide five ways of exchanging information. You can:

• Import or link an Excel worksheet.

• Format and print Excel data in Access reports.

• Move Excel data into Access.

• Create a PivotTable control in an Access form.

• Open the output of an Access datasheet, form, or report in Excel automatically.

For information about importing and linking external data files, see "Importing or Linking Data from External Data Sources" earlier in this chapter. The remaining four methods of exchanging data with Excel are described in the following sections.

Formatting and Printing Microsoft Excel Data in Microsoft Access Reports

When you need the grouping and formatting functionality of Access reports for data in an Excel worksheet, you can use the Access Report command on the Excel Data menu to start the Access Report Wizard. For more information, see Excel online Help.

Moving Microsoft Excel Data into Microsoft Access

When an Excel worksheet is no longer the appropriate tool for your data — for example, if you require more rows than are practical, or if you need to have the data in your worksheet updated by many users — you can create an Access database from the worksheet. To do so, use the Convert To Access command on the Excel Data menu. For more information, see Excel online Help.

Creating a PivotTable Control in a Microsoft Access Form

If a computer has both Access and Excel installed, you can create a PivotTable control in an Access form. Using the PivotTable Wizard, you can create a control on a form that allows you to summarize large amounts of data using a format and calculation method you choose. A PivotTable is like a crosstab query, but you can switch the row and column headings dynamically to see a different view of the data.

To create an Excel PivotTable on an Access form

1. On the Access Insert menu, click Form.

2. In the New Form dialog box, click PivotTable Wizard, and then click OK.

3. Follow the instructions in the wizard.

Note   The source data that the PivotTable uses is not automatically saved with the PivotTable or form. Instead, you must update the PivotTable with the most current data each time you use it, which means that the location where the data is stored must be accessible. If the data source will not be available later, when you want to edit the PivotTable, save a copy of the data with the PivotTable.

Opening Microsoft Access Output in Microsoft Excel Automatically

You can save the output of a datasheet, form, or report as an XLS file and automatically open it in Excel. Doing so preserves most formatting, such as fonts and colors. Report group levels are saved as Excel outline levels. A form is saved as a table of data.

To open the output of a datasheet, form, or report in Microsoft Excel

1. In the Access Database window, click the name of the table, query, form, or report you want to save and open in Excel.

   – or –

   To save a portion of a datasheet, open the datasheet, and then select the portion of the datasheet.

2. On the Tools menu, point to OfficeLinks, and then click Analyze It With MS Excel.

   The output is saved as an Excel XLS file in the folder where Access is installed. Excel automatically starts and opens it.

Dragging Information Between Microsoft Access and Microsoft Excel or Word

The following drag­and­drop functionality is available in Access. You can:

• Drag database objects between open Access databases.

• Drag Access tables and queries into Word and Excel.

• Create a table by dragging a range of cells from an Excel worksheet into the Database window.

• Drag OLE objects into an OLE Object field in a form in Form view, or in a form or report in Design view.

Using Microsoft Windows Messaging Service Data with Microsoft Access

If the Microsoft Messaging driver is installed, you can use Access to link Exchange Client or Outlook data that is stored either on an Exchange server or in local Personal Folder (PST) or Personal Address Book (PAB) files. Exchange Client and Outlook data is collectively referred to as Windows Messaging Service data. You can link or import Exchange Client or Outlook data in Access by using the Import Exchange/Outlook Wizard and the Link Exchange/Outlook Wizard, which are available on the World Wide Web. Installing these wizards installs the Microsoft Messaging driver. You can also link or import Exchange Client or Outlook data in Access by using DAO code in Visual Basic.

http://www.microsoft.com/AccessDev/FreeStuff/ExchWiz.htm

For information on using DAO code in Visual Basic to link or import Exhange Client or Outlook data see the article "Accessing Microsoft Exchange and Microsoft Outlook Data Using Visual Basic" which is available from the Microsoft Office Developer forum Web Site at:

http://www.microsoft.com/AccessDev/Articles/ExcOutlk.htm

Modifying Windows Registry Settings for External Data Formats

When you install Access or external database drivers, Setup writes associated entries to various keys in the Windows registry. Setup writes a set of default values for each installed ISAM engine in a subkey of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines key. These settings apply to any host application that uses the Jet database engine on the current computer, which can include applications such as Excel and Visual Basic applications in addition to Access.

You can create settings for an ISAM engine that apply only to Access by creating a corresponding subkey below the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines key and then specifying any values you want to override in that subkey. These multiple registrations allow each host application to specify values that apply to its instance of the Jet database engine. If a host­specific registration omits particular values, these settings default to the values registered in the \Microsoft\Jet\3.5\Engines key.

Although Setup writes logical defaults for the Windows registry entries, your particular environment or preferences might require you to change entries. For information about all registry entries, see Appendix C, "Registry Keys and Values."

Note   For the new settings to take effect after you change your initialization settings, you must quit the application and then restart it, using the Jet database engine.

Using Microsoft Access with ODBC and Client/Server Operations

In a client/server application, you store your data in remote tables on a database server, such as SQL Server, instead of in local tables in your Access database. Your Access database (the client) sends queries and updates to the server and retrieves the data it needs. A client/server application takes advantage of the processing power available to both the client and the database server components.

By contrast, in a file server application, data simply resides on a network file server, transactions with the data are handled no differently than the reading and writing of any other file on the network, and all processing is performed locally.

Developing, optimizing, and maintaining client/server applications is a complex subject. This section briefly describes what ODBC is and how it allows you to interact with a large variety of SQL data sources. The reasons to adopt a client/server approach are covered, as are the issues involved in converting an existing Access application to a client/server application. For more information about developing client/server applications using Access, see Building Applications with Microsoft Access 97.

Understanding ODBC

Providing data access to the large variety of database applications can be very complex. Applications that use the ODBC standard, such as Access and Excel, ease this burden by using a vendor­neutral means of working with database management systems (DBMSs). Microsoft has gained very broad support for ODBC, which allows you to use a broad variety of data sources.

ODBC contributes many significant benefits by providing an open, standard way to use data. ODBC allows the following:

• User access to data in more than one data storage location (for example, on more than one server) from within a single application.

• User access to data in more than one type of DBMS (such as DB2, ORACLE, DEC RDB, Apple DAL, dBASE, and SQL Server client/server) from within a single application.

• Simpler application development — it is now easier for developers to provide access to data in multiple, concurrent DBMSs.

• A portable application programming interface (API), allowing the same interface and access technology to be a cross­platform tool.

• Applications insulated from changes to underlying network and DBMS versions. Modifications to networking transports, servers, and DBMSs do not create problems for current ODBC applications.

• Use of SQL — the standard language for DBMSs.

• Protection of corporations' investments in existing DBMSs and developers' acquired DBMS skills. ODBC also allows corporations to continue to use existing diverse DBMSs, while developing applications using other systems that are more appropriate to the task at hand.

How ODBC Works

ODBC defines an API. Each ODBC application uses the same code, as defined by the API specification, to exchange information with many types of data sources through DBMS­specific drivers. A driver manager sits between the applications and the drivers. In Windows, the driver manager and the drivers are implemented as dynamic­link libraries (DLLs). The following illustration outlines the process.

The application calls ODBC functions to connect to a data source, send and receive data, and disconnect. The driver manager provides an application with information (such as a list of available data sources), loads drivers dynamically as they are needed, and provides argument and state transition checking. The DBMS driver, developed separately from the application, processes ODBC function calls, manages all exchanges between an application and a specific DBMS, and may translate the standard SQL syntax into the native SQL of the target data source. All SQL translations are the responsibility of the driver developer.

Applications are not limited to communicating through one driver. A single application can make multiple connections, each through a different driver, or multiple connections to similar sources through a single driver. To use a new DBMS, a user or an administrator simply installs a driver for the DBMS. The user does not need a different version of the application to use the new DBMS. This is a tremendous benefit for end users, and it also translates into significant savings in support and development costs.

Using ODBC Data in Microsoft Office Applications

ODBC data is available to Office applications in the following ways:

• In Access 97, by importing from or linking to ODBC data sources using the commands on the Get External Data submenu (File menu). Access can also export to available ODBC data formats using the Save As/Export command (File menu).

• In Excel 97, by using the macro commands and functions available through the ODBC add­in, when building PivotTables using the Pivot Table command (Data menu); and in Query using the Get External Data command (Data menu).

• When creating custom applications in either Access or Excel using Visual Basic and DAO code.

The following section describes using Access to create a client/server application. For more information about using Excel to gain access to ODBC data, see "Sharing Data Between Excel and SQL Server" and "Using Microsoft Excel and Microsoft Query" later in this chapter.

Using Client/Server Architecture

The three primary reasons for adopting client/server architecture for database applications are:

• Allowing enterprise­wide access to corporate information

  As computers become smaller, more powerful, and more usable, information is more widely distributed across a corporate enterprise in an increasing number of formats. Client/server technology provides the links to read and manipulate data regardless of its location or storage format.

• Upsizing multiuser file server database applications

  Client/server applications provide improved performance, security, and reliability. Database application solutions can grow and become more complex over time. Client/server architecture provides a way to scale up a database application when it becomes larger and more complex and must support more users and a higher volume of transactions.

• Downsizing mainframe and minicomputer database applications

  The managers and developers of many mainframe database applications move their applications to client/server architecture to take advantage of graphical user interface (GUI) processing. Access provides an ideal environment for developing user­driven systems. At the same time, Access provides many powerful features for interacting with external data sources. Also motivating this change is the reduced cost of new back­end servers.

  Downsizing, however, is a more difficult and a much slower migration process. As such, the seamless integration from the mainframe world to the client/server world is a priority. When this level of integration is achieved, client/server applications enhance and add value to existing mainframe­based processes.

Upsizing a Microsoft Access File Server Application to a Client/Server Application

A high­level summary of steps involved in migrating from a file server to a client/server environment include:

1. Create the server database.

2. Establish your ODBC data source using the ODBC Administrator.

3. Document your existing Access database.

4. Export each table to the server database.

5. Add server­based integrity constraints: validation rules, default values, and referential integrity.

6. Add the indexes to the server tables with the necessary attributes.

7. Link the server tables.

If you want to upsize an Access database to SQL Server, the simplest way to perform this process is by using the Upsizing Wizard included with the Access Upsizing Tools. The Upsizing Wizard preserves your database structure, including data, indexes, field DefaultValue property settings, and AutoNumber fields. It also converts your Access validation rules and defaults to SQL Server equivalents, and maintains your table relationships and referential integrity after upsizing.

http://www.microsoft.com/accessdev/prodinfo/aut97dat.htm

The remainder of this section explains these steps in more detail. For more information about these procedures and about optimizing a client/server application, see Building Applications with Microsoft Access 97.

Before You Begin

To ensure that your Access applications scale up easily to a client/server database, the following practices are recommended:

• Do not embed spaces in your table names.

  Most servers cannot support embedded spaces. During export, Access replaces spaces with underscores in the name, which means that references to those fields in queries, forms, reports, expressions, and Visual Basic code will fail.

• Create a name­mapping query if your application has tables and fields with names containing embedded spaces.

  A name­mapping query is saved as the original table name that includes spaces, but it points to the server table without spaces. If a field name contains embedded spaces, you can map the Access field name to the SQL Server column name without spaces using the convention Field Name:FIELD_NAME in Field row of the query definition.

• Be consistent in the use of case in your object names.

  Some SQL servers are case­sensitive, but Access default behavior is not case­sensitive.

• When using DAO code in Visual Basic, do not use the table­type Recordset to manipulate remote tables; always use the dynaset­type Recordset instead.

  Tables linked using the Link Tables command (File menu) automatically use dynaset­type Recordsets.

Creating the Server Database

The method for creating a server database depends on the SQL server software you are using. For details, see your SQL server documentation. If you have an existing SQL database that you want to use as the basis for your client/server application, you can export or link tables from Access after you have defined this database as an ODBC data source.

Defining Your ODBC Data Source

Use the 32bit ODBC icon in Windows Control Panel to define your ODBC data source. The 32bit ODBC icon is available only when you install the SQL Driver with Office or Access. Before you can specify a data source, you must have the appropriate ODBC driver for your database server installed.

You can see which ODBC drivers are available on a computer by double­clicking the 32bit ODBC icon in Control Panel and then clicking the Drivers button. Office Setup, as well as the Setup programs for standalone versions of Access and Excel, include the SQL Server driver and support files for the 32bit ODBC Control Panel icon. If the 32bit ODBC icon is not available in Control Panel, rerun Setup and click Add/Remove. Under the Data Access option, select the Microsoft SQL Server Driver check box.

If you are using an SQL database server other than SQL Server, contact the vendor of that product to find out about the availability of an ODBC driver. ODBC drivers are also available for data formats other than SQL servers. For example, there is an ODBC driver for Lotus Notes databases. Contact the vendor of the product whose data you want to use for information about the availability of an ODBC driver.

Important    Office 97 applications can only use 32­bit versions of ODBC drivers. If a previous version of an Office application, such as Access version 2.0, was installed on a computer, older 16­bit versions of ODBC drivers may still be present. To use the ODBC drivers available with Office 97 applications, rerun Setup and install the drivers you need. Similarly, other applications written for Windows 3.x may have 16­bit versions of ODBC drivers. Contact the vendor of the product whose data you want to use for information about a current 32­bit ODBC driver.

Documenting Your Existing Microsoft Access Database

Indexes, validation rules (also referred to as business rules, domain integrity rules, or database­specific rules), default values, and referential integrity rules are not created when Access exports tables to a client/server database. After exporting, you need to define the indexes, validation rules, and default values on the server tables themselves. If permissions vary for users of the database, you will need this information, too. You can print a comprehensive list of the properties of the tables in your MDB file by opening the database with Access and then using the Documenter command (Tools menu, Analyze submenu).

Exporting Microsoft Access Tables to the Server Database

Creating the structure of a complex, multitable, client/server database can be a tedious and time­consuming process. Access table export features can speed up this process greatly. Export each table to the server database using the Export/Save As command (File menu).

There are trade­offs to consider when exporting data. Access exports each row one at a time. This ensures that your exported data adheres to all the server­based rules, but it is significantly slower with extremely large tables than using server­based, bulk­copy routines.

If your tables contain a few thousand records or fewer, it is usually faster to export the tables from the Access database to the server database than to use bulk­copy programs, such as BCP for SQL Server, with text files. If you do use a batch copy program to load tables on your server, you can test data type and field size consistency before running the batch copy program by exporting at least one record from each table to the server using Access.

Most migrations to client/server operation are accomplished in two phases: test and production. To limit the number of records used for the test phase, write a select query in Access and then export data from that query.

Adding Server­Based Validation Rules

Validation rules are not exported with a table. You can create triggers or rules on the server table that enforce your rules, or you can define form­level validation rules in your Access forms instead. Using form­level rules is easier but not as reliable, because invalid entries are rejected only by the form, not by the server table itself.

A trigger is a piece of code residing on the server that executes an SQL statement prior to the occurrence of a specified event, such as INSERT, DELETE, or UPDATE. If you are using a server­based trigger, be aware that many Visual Basic functions, such as the DatePart or Format function, may have no equivalent on your server.

Adding Default Values and Enforcing Referential Integrity

Most client/server DBMSs provide a default value property. Otherwise, you need to use stored procedures to set default values for fields whose value is not supplied during the append process. If you need to emulate the Required property of fields in Access tables, you can use the NOT NULL command in the definition of your SQL table.

If the server database does not support ANSI SQL reserved words, you need to write stored procedures to enforce referential integrity and perform cascading updates and deletions. None of the rules and stored procedures you create are visible to Access; if your application violates the rules, it receives a message from the server.

Adding Indexes

Add the indexes to the server tables with equivalent attributes to those in the Access database — for example, PrimaryKey, No Duplicates, or No Nulls. Access cannot update a table that does not have a unique index.

In Access 2.0 or later, dynaset­type Recordsets support server­based primary key generation from triggers. In earlier versions, as the primary key was generated during an insert, the recordset cursor would act as if the record had been deleted because it was no longer in the same location. Access now re­fetches the record, keeping the recordset up­to­date. This is particularly significant because many developers use the convenient AutoNumber field (called a Counter field in previous versions) to generate their primary keys. The equivalent of the Access AutoNumber field data type seldom is found in client/server DBMSs, yet it can be critical to your application.

This improvement allows you to create an INSERT trigger on the server table that increments the integer value of a field each time a new record is appended to the table. The Transact­SQL statement that creates the equivalent of an AutoNumber field is shown in the following example:

CREATE TRIGGER add_customer_id ON dbo.customers
FOR INSERT AS 
UPDATE dbo.customers
SET inserted.Customer_ID = (SELECT MAX(Customer_ID)
FROM dbo.customers) + 1
WHERE dbo.customers.customer_ID IS NULL

When you use a trigger to create the AutoNumber field equivalent, you do not see the generated value in a bound control until you complete the appending process for the record.

Linking Server Tables

Use the Link Tables command (File menu, Get External Data submenu) to link tables in the server database. When you link a table, Access stores a copy of the table structure in your Access database system tables. This allows you to build queries, forms, and reports as if the linked table were part of your database.

After linking, relationships between tables need to be established in the Relationships window. This establishes the default join conditions and types used when building new queries.

Any operation in Datasheet view that is permissible for a native Access table can generally be performed for tables linked by ODBC. In Design view, you can set the values of the Format, InputMask,
and Caption properties. All other properties of linked tables are read­only.

Renaming Linked Tables

When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.

Excel fully supports copying and pasting linked or unlinked data, as described in the "Overview" earlier in this chapter. In addition, Excel includes several SQL functions for working with external data. Once the data has been imported, Excel offers powerful data analysis and reporting tools such as PivotTables. This section describes the tools available for working with external data in Excel.

Sharing Data Between Excel and SQL Server

The ODBC add­in for Excel, Xlodbc.xla, provides ODBC functions that allow you to connect to SQL Server data sources directly. The following is a list of the functions provided by the ODBC add­in and a description of the arguments for those functions. The arguments for each function are shown here as they are displayed in the Paste Function dialog box (Insert menu).

• SQLBind(ConnectionNum,Column,Reference)

• SQLClose(ConnectionNum)

• SQLError()

• SQLExecQuery(ConnectionNum,QueryText)

• SQLGetSchema(ConnectionNum,TypeNum,QualifierText)

• SQLOpen(ConnectionStr,OutputRef,DriverPrompt)

• SQLRequest(ConnectionStr,QueryText,OutputRef, DriverPrompt,ColNamesLogical)

• SQLRetrieve(ConnectionNum,DestinationRef,MaxColumns,MaxRows, ColNamesLogical,RowNumsLogical,NamedRngLogical,FetchFirstLogical)

• SQLRetrieveToFile(ConnectionNum,Destination,ColNamesLogical,ColumnDelimiter)

Note   The functions SQLBind, SQLClose, SQLError, SQLExecQuery, SQLGetSchema, SQLOpen, SQLRetrieve, and SQLRetrieveToFile are displayed in the Paste Function dialog box (Insert menu) when the active sheet is a worksheet. However, these functions can be used only in a macro or module sheet.

For more information about a specific function, see Excel online Help. For more information about ODBC, see "Using Microsoft Access with ODBC and Client/Server Operations" earlier in this chapter.

Using Microsoft Excel and Microsoft Query

To use Query with Excel, you must install the Query application, the Query add­in, the drivers for the types of data you want to retrieve, and the necessary ODBC files. To install these files, rerun Setup and click Add/Remove; then select the Data Access option. For information about Data Access components, see Appendix D, "List of Installed Components."

When you create a PivotTable that uses an external data source, use Query. In most cases, you can create PivotTables that use data supplied by Query and distribute your PivotTables to other users who can manipulate or refresh the PivotTables. However, if you create a PivotTable that uses either the Server Based Page Fields or the Optimize Memory options, Excel requires that Query be installed on the same computer. If you distribute such a PivotTable to users in your workgroup, they must have Query installed to use these options.

Query is not installed when you choose the Typical installation during Setup. To install Query, choose the Custom installation or rerun Setup and click Add Remove; then select the Microsoft Query option. For more information about custom installations for a workgroup, see Chapter 6, "Customizing Client Installations."

Exporting Microsoft Excel Data to Lotus Notes

When you install Office, Setup detects whether Lotus Notes version 3.x or 4.x (16­bit or 32­bit) is installed on the computer. If either of them is installed, Setup automatically installs Lotus Notes converters for Excel and Word. With these text converters, you can import Word and Excel documents into a Lotus Notes memo. For more information about working with Lotus Notes, see "Interoperability with Lotus Notes/FX" in Chapter 28, "Working with Messaging Systems and Connectivity Software."

The Outlook Journal folder can automatically track documents created by other Office applications. When Office applications are installed, Setup adds the application names to the Also record files from box on the Journal tab in the Options dialog box (Tools menu) in Outlook. When you run Outlook, select the applications whose documents you want to track in this dialog box.

When one of the applications you have selected opens a file, it creates a journal entry with the current time and date and a link to the document. When the application closes the file, Outlook updates the journal entry and records the amount of time you worked on the file in the Duration box.

You can also create a journal entry for a document manually by dragging the document (in Windows Explorer or in Outlook) into the Journal folder.

User\Outlook\Tools_Options\Journal\Automatically record these items

User\Outlook\Tools_Options\Journal\Also record these files

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Creating Tasks from Office Applications

As you work in an Office application, you can add a task in the Outlook Tasks folder that corresponds to the work you are doing. For example, while working on a Word document, you can create a task to remind you to have the document reviewed by a certain date. Once the task is created, you can assign the task to others and track its progress.

Importing and Exporting Items

You can use the Import and Export Wizard to import data from other applications into Outlook folders or to export data from Outlook folders to other applications. Outlook can import or export data for several applications and in different formats.

If you need to import data to or export data from an application that Outlook does not support directly, you may be able to use one of the intermediary formats that Outlook does support. The following procedures demonstrate how to import a data file from an unsupported application into an Outlook folder.

To save your data in an intermediary file format

1. In your application, save the data in a comma­delimited or tab­delimited text file.

   Be sure to include a heading line in the text file that indicates the names of the data fields in each column, as shown in the example later in this section.

2. Edit the heading line so that column names match Outlook field names, as shown in the example.

Now you can import the data into Outlook.

To import an intermediary data file into an Outlook folder

1. On the Outlook File menu, click Import and Export.

2. In the Choose an action to perform box, select Import from Schedule+ or another program or file.

3. In the Select file type to import from box, select the appropriate file format, based on the characteristics of your data file.

4. In the File to import box, enter the name and path of the text file.

5. In the same panel of the wizard, choose how you want Outlook to deal with items in your text file that duplicate items already in the Outlook folder by selecting one of the following options:

   • Replace duplicates with items imported. Outlook overwrites duplicate items with those from your data file.

   • Allow duplicates to be created. Outlook adds all of your data, even if there are duplicates.

   • Do not import duplicate items. Outlook does not add an item from your file if it is a duplicate of an item that already exists in the Outlook folder.

6. In the Select destination folder box, click the Outlook folder into which you want your data imported.

   The folder you select must match the type of data you are importing. For example, if your data consists of names and addresses, you must import it into a folder with contact items, such as the Contacts folder.

7. Click Map Custom Fields.

   By default, Outlook maps the data from your file based on the field names you entered in the heading line of the file. If you do not have a heading line in your file, then Outlook imports the fields based on their position in the file. Change or correct these defaults in the Map Custom Fields dialog box, and then click OK.

8. Click Finish to import the data.

You can also export data from Outlook to your application, if your application can read comma­delimited or tab­delimited text files. Outlook exports data by writing every field into the output file in the order shown in the tables later in this section.

Example of a Data File Imported to Outlook

Suppose you have an employee list in an address book program, and you want to import the addresses into the Outlook Contacts folder. The program can save its data in a text file with fields separated by commas.

First, output the data from your application into a text file in the following format. Be sure to include a heading line as the first line of the output file, giving the names of the data fields.

COUNTRY, CITY, LASTNAME, FIRSTNAME, EXTENSION
UK, London, Dalal, Ketan, 452
UK, London, King, Robert, 465
UK, London, Chai, Sean, 428
UK, London, Buchanan, Steven, 3453
USA, Kirkland, Boyd, Shannon, 3355
USA, Redmond, Dunn, Michael, 5176
USA, Seattle, Callahan, Laura, 2344
USA, Seattle, Rudd, Darlene, 5467
USA, Tacoma, Conroy, Stephanie, 3457

Then change the names in the heading line to match the field names for contact items in Outlook, as shown in the following table.

Change this name	To this equivalent Outlook field name
COUNTRY	Business Country
CITY	Business City
LASTNAME	Last Name
FIRSTNAME	First Name
EXTENSION	Business Phone

When you are finished, your heading line should look like this:

Business Country, Business City, Last Name, First Name, Business Phone

Now, run Outlook and import the file. Click Map Custom Fields to verify that Outlook has mapped your data correctly.

If you do not have a heading line in your data file, you can clear the check box First record contains field names in the Map Custom Fields dialog box, and Outlook maps your data to Outlook fields in the order in which the data appears in the file. For example, without a heading line, you would type the data in the sample data file as shown here (fields containing no data are left blank):

,,Ketan,,Dalal,,,,,,,,,London,,,UK,,,,,,,,,,,,,,,,,,,452

How Outlook Maps Field Names from the Data File

The following tables show the field names for each Outlook item type and the order in which they are mapped (if there is no heading line in the data file).

Calendar Item Fields

Subject

Start Date

Start Time

End Date

End Time

All day event

Reminder On/Off

Reminder Date

Reminder Time

Meeting Organizer

Required Attendees

Optional Attendees

Meeting Resources

Billing

Categories

Description

Location

Mileage

Priority

Private

Show Time As

Contact Item Fields

Name

Title

First Name

Middle Name

Last Name

Suffix

Company

Department

Job Title

Business Address

Business Street

Business Street 2

Business Street 3

Business City

Business State

Business Postal Code

Business Country

Home Address

Home Street

Home Street 2

Home Street 3

Home City

Home State

Home Postal Code

Home Country

Other Address

Other Street

Other Street 2

Other Street 3

Other City

Other State

Other Postal Code

Other Country

Assistant's Phone

Business Fax

Business Phone

Business Phone 2

Callback

Car Phone

Company Main Phone

Home Fax

Home Phone

Home Phone 2

ISDN

Mobile Phone

Other Fax

Other Phone

Pager

Primary Phone

Radio Phone

TTY/TDD Phone

Telex

Account

Anniversary

Assistant's Name

Billing

Birthday

Categories

Children

E­mail 1

E­mail 1 Address

E­mail 1 Type

E­mail 1 Service Code

E­mail 1 Display Name

E­mail 2

E­mail 2 Address

E­mail 2 Type

E­mail 2 Service Code

E­mail 2 Display Name

E­mail 3

E­mail 3 Address

E­mail 3 Type

E­mail 3 Service Code

E­mail 3 Display Name

Gender

Government ID Number

Hobby

Initials

Keywords

Language

Location

Mileage

Notes

Office Location

Organizational ID Number

PO Box

Private

Profession

Referred By

Spouse

User 1

User 2

User 3

User 4

Web Page

Note   If you specify a field name with multiple elements in your data file, Outlook attempts to parse the data into its separate parts. For example, if you specify Name as the field name in the heading line of your data file, and the data is Stephanie Conroy, Outlook places Stephanie in the First Name field and Conroy in the Last Name field.

Using Outlook with PowerPoint

PowerPoint includes features designed to work with Outlook. For example, you can use Outlook to schedule a meeting with your colleagues. Then, when the meeting is over, you can use Outlook to send action items to assigned individuals. In both cases, you run Outlook using the Meeting Minder command (Tools menu) in PowerPoint. If Outlook is not installed on your computer, this command is not available.

Scheduling a PowerPoint Meeting Through Outlook

In PowerPoint, you schedule meetings through Outlook by using the Meeting Minder feature.

To schedule a PowerPoint meeting through Outlook

1. On the Tools menu, click Meeting Minder.

   – or –

   If you are delivering a presentation, right­click in the presentation and point to Meeting Minder.

2. Click Schedule.

   PowerPoint runs Outlook and opens a blank appointment.

3. On the Appointment menu, click Invite Attendees.

4. In the To box, enter the names of people you want to invite to the meeting.

   – or –

   If you need to find attendees' names in your address book, click To.

5. If you want to schedule the meeting based on attendees' availability, click the Meeting Planner tab, and select the Show attendee availability option.

6. Select start and end times for the meeting in the Start time and End time boxes on the Appointment tab.

   – or –

   Click AutoPick on the Meeting Planner tab for Outlook to find a block of free time for all required attendees.

7. To invite the attendees, click Send.

For more information about scheduling appointments in Outlook, see Outlook online Help. For information about how Outlook reads attendees' free/busy times, see Chapter 31, "Workgroup Features in Microsoft Outlook."

Exporting Action Items to Outlook

If you have been using Meeting Minder to record action items during a presentation, you can export the action items to your task list in Outlook. From your task list, you can mail each action item to the person responsible for completing the item.

To export action items from PowerPoint to Outlook

1. In Meeting Minder, click Export.

2. Select the Post action items to Microsoft Outlook check box, and click Export Now.

   PowerPoint starts Outlook and creates a new task for each action item in your task list, as shown in the following illustration.

   
   

In Outlook, you can send each of these tasks as an electronic mail (e­mail) message by dragging the task to the Outbox.

To mail the action items

1. To display the mail icons, click the Mail button on the Outlook bar.

2. Click a task in the task list, and drag it to the Outbox icon on the Outlook bar.

   Outlook starts a new message with formatted task information.

3. Enter the recipient's e­mail address on the To line, and click Send.

   – or –

   If you need help resolving the name of the recipient, click To and browse your address book for the name.

The message started by Outlook looks similar to the following illustration. The subject and due date are carried over from the task list, but the recipient name is not generated automatically.

When recipients receive an action item as a message, they can drag the action item from their Inbox to their task list in Outlook. Storing action items as tasks in Outlook helps attendees keep track of their progress toward completing the action items.

PowerPoint fully supports copying and pasting linked or unlinked data, as described in the "Overview" earlier in this chapter. In addition, PowerPoint includes special features for importing presentations from and exporting them to Word.

Importing Outlines from Word

PowerPoint determines the outline structure for the presentation based on the paragraph formatting of the imported document, as follows:

• By heading styles

  If the paragraphs are formatted as heading styles in Word, PowerPoint structures the presentation outline according to the headings. Every Heading 1 becomes the title for a new slide, Heading 2 becomes the first level of text, and so forth. Text formatted as anything other than a heading style is not imported to the presentation.

• By indented paragraphs

  If the paragraphs are not styled with heading styles, PowerPoint structures the presentation outline according to how the paragraphs are indented. Every paragraph flush with the left margin becomes the title for a new slide, paragraphs indented once become the first level of text, and so forth.

• By leading tabs

  If the paragraphs are indented with leading tabs — as in a plain text file — PowerPoint uses the number of leading tabs to determine the outline structure. Every paragraph with no leading tab becomes the title for a new slide, paragraphs with one leading tab become the first level of text, and so forth.

Note   PowerPoint supports five levels of text. If the imported outline has more than five levels, levels six and beyond are imported as level­five text in PowerPoint.

The following illustration shows a Word outline imported into PowerPoint.

Exporting Presentations to Word

You can export presentations to Word using the Write­Up feature in PowerPoint, and you can export meeting minutes and action items to Word by using the export feature in Meeting Minder. This section describes how these features work.

Using Write­Up

Use the Write­Up dialog box to place slides and speaker note text on pages according to options you specify in PowerPoint, as follows. (To display the dialog box, click Send To on the File menu, then click Microsoft Word.)

• Notes next to slides

  If you select the Notes next to slides option, Word creates a three­column table for the slides and notes. For each slide, Word places the slide number in the first column, the slide in the second column, and the note text in the third column.

• Notes below slides

  If you select the Notes below slides option, Word places the slides and notes in separate paragraphs. For each slide, Word generates a page with the slide number in the first paragraph, the slide in the second paragraph, and the note text in the third paragraph.

• Outline only

  If you select the Outline only option, Word creates an outline of the presentation text and does not import slide images. Slide titles are converted to Heading 1 style paragraphs, first­level text is converted to Heading 2 style paragraphs, and so forth.

  Note   You can also select the Blank lines next to slides or Blank lines below slides option to print a series of blank lines in place of speaker notes text, so that audience members can jot down their own notes.

To export PowerPoint presentations to Word

1. On the PowerPoint File menu, point to Send To, and then click Microsoft Word.

2. In the Write­Up dialog box, select the layout you want.

3. To embed the slides in the Word document, select Paste.

   – or –

   To link the slides, select Paste link.

PowerPoint starts Word, opens a new document based on the Normal template, and exports the presentation to Word, as shown in the following illustration.

Embedding or Linking Slides (Windows only) You can either embed or link the presentation slides in the Word document, with the following results: If you embed PowerPoint slides, the resulting Word document increases substantially in file size and is not updated to reflect changes either to the slides or to the location of the presentation file. If you link PowerPoint slides, the resulting Word document remains small and is updated to reflect changes both to the slides and to the location of the presentation file.

Using Meeting Minder

With Meeting Minder, you can format, edit, and print meeting minutes and action items by exporting them to a Word document. Word places all the meeting minutes and action items for the presentation in a single document, where you can edit it before printing or distributing it.

Note   You can use Outlook to send action items to the appropriate people. For information about using Outlook with PowerPoint, see Chapter 28, "Working with Messaging Systems and Connectivity Software."

To export meeting minutes and action items to Word

1. On the Tools menu, click Meeting Minder.

   – or –

   If you are running a slide show, right­click in the presentation, and point to Meeting Minder.

2. Click Export, select the Send meeting minutes and action items to Microsoft Word check box, and click Export Now.

   PowerPoint starts Word, opens a new document based on the Normal template, and exports the meeting minutes and action items to Word, as shown in the following illustration.

   

Word fully supports copying and pasting linked or unlinked data, as described in the "Overview" earlier in this chapter. In addition, Word includes text converters for importing Excel and Lotus 1­2­3 worksheet data into tables, and for importing Word documents into Lotus Notes.

Note   Word also supports the conversion of Word documents to HTML for intranets or the Internet. For more information, see "Web Support in Microsoft Word 97" in Chapter 25, "Web Support in Microsoft Office Applications."

Importing Worksheets from Microsoft Excel

You can open or import Excel versions 2.x – 97 worksheets or named ranges into Word. In Word, the worksheet is converted to a table in its own document.

To import an Excel worksheet or named range into Word

1. On the Word File menu, click Open.

   – or –

   On the Insert menu, click File.

2. Select the Excel workbook that contains the data you want to import, and then click Open.

3. If the Convert File dialog box appears, select Microsoft Excel Worksheet.

4. In the Open Worksheet dialog box, select the worksheet that contains the data you want to import. If the data is in a specific named range, select that.

The Excel text converter functions in one direction only: Data can be brought into Word from Excel worksheets but cannot be saved in Excel format.

Formatting Features

The following table describes conversion issues that may affect your data or formatting when you import Excel worksheets into Word.

When this Excel feature	Is imported into Word
Fill	Specified fill characters become left­aligned.
Row height	Each row takes on the height of the cell that contains the most text.
Column width	Most columns retain their width. Hidden columns and zero­width columns become a standard width.
Hidden columns	Hidden columns are converted as regular table columns.

Numeric Formatting Features

The following table describes conversion issues that may affect your data or formatting when you import Excel worksheets into Word.

When this Excel feature	Is imported into Word
Regular Excel formats	Excel tries to fit numbers in General format into the current column width. These numbers are brought in at full precision. This may result in more decimal places than are displayed on the worksheet.
User­defined formats	The question mark (?) and asterisk (*) characters are not supported when used to define fractional number formats. For example, if the value of a cell is 0.5 and the user­defined number format is # ?/?, Excel displays 1/2; Word displays 0.5.

Worksheet Size

The following table describes conversion issues that may affect your data or formatting when you import Excel worksheets into Word.

When this Excel feature	Is imported into Word
Fewer than 32 columns	A table is created in Word.
More than 32 columns or wider than 22 inches	The worksheet is imported into Word as tab­delimited text.

Note    Excel 4.0 allows you to press ALT+ENTER to begin a new line within a text cell. The resulting new­line character is translated to a paragraph mark in a table if the original Excel worksheet width is 32 columns or fewer, or to a new­line character in tab­delimited text. These conversions ensure mail­merge compatibility.

Importing Worksheets from Lotus 1­2­3

You can open or import Lotus 1­2­3 2.x – 4.0 worksheets into Word. Once in Word, the worksheet is converted to a table. The Lotus 1­2­3 text converter functions in one direction only: Data can be brought into Word from Lotus 1­2­3 worksheets but cannot be saved in Excel format.

The procedure for importing Lotus 1­2­3 worksheets into Word is the same as the procedure for importing Excel worksheets into Word, as described earlier.

Formatting Features

The following table describes conversion issues that may affect your data or formatting when you import Lotus 1­2­3 worksheets into Word.

When this Lotus 1­2­3 feature	Is imported into Word
Font	All Lotus 1­2­3 data becomes 10­point Courier font.
Numeric formatting	Lotus 1­2­3 tries to fit numbers in General format into the current column width. These numbers are brought in at full precision. This may result in more decimal places than are displayed on the worksheet.

Worksheet Size

The following table describes import issues that may affect your data or formatting when you import Lotus 1­2­3 worksheets into Word.

When this Lotus 1­2­3 feature	Is imported into Word
Fewer than 32 columns	A table is created in Word.
More than 32 columns or wider than 22 inches	The worksheet is imported into Word as tab­delimited text.

Other Features

The following table describes import issues that may affect your data or formatting when you import Lotus 1­2­3 worksheets into Word.

When this Lotus 1­2­3 feature	Is imported into Word
Ranges	The converter displays a dialog box asking you to choose from a list of range names. Type a Lotus 1­2­3 range (b2..g43) or choose an existing range name (my_data), or press ENTER to convert the entire worksheet. Note that 3­D range names are supported correctly, but explicit 3­D range definitions (for example, a:a1..c:c5) are not supported.
Password	If the worksheet is password protected, you must enter the password. Note that you may have to reenter the password when beginning a print merge operation. Password­protected files created in Lotus 1­2­3 version 3.x cannot be converted.
Empty files	Empty worksheets are imported into Word as a single empty cell.

Note   Word can import PIC graphics files created by Lotus 1­2­3 and Borland Quattro Pro. Word cannot import Micrografx PIC or Draw Plus PIC files.

Exporting Word Outlines to PowerPoint

You can export the outline of a Word document to PowerPoint. If the paragraphs are formatted as heading styles in Word, PowerPoint structures the presentation outline according to the heading styles. Every Heading 1 paragraph becomes the title for a new slide, a Heading 2 paragraph becomes the first level of text, and so forth. Text formatted as anything other than a heading style is not imported to the presentation.

If the paragraphs are not styled with heading styles, PowerPoint structures the presentation outline according to the way the paragraphs are indented. For more information about how PowerPoint structures an imported Word document, see "Sharing Information with PowerPoint" earlier in this chapter.

To export a Word outline to PowerPoint

• On the Word File menu, point to Send to, and then click Microsoft PowerPoint.

  Word exports the heading­level paragraphs to PowerPoint. The Word document opens in PowerPoint as a new presentation in outline view.

After you export the outline, you can add additional text to the presentation in PowerPoint, but there is no link back to the outline in the original Word document.

Exporting Word Documents to Lotus Notes

When you install Office, Setup detects whether Lotus Notes 3.x or 4.x (16­bit or 32­bit) is installed on the computer. If it is, Setup automatically installs Lotus Notes converters for Excel and Word. With these text converters, you can import Word and Excel documents into a Notes memo. For more information about working with Lotus Notes, see "Interoperability with Lotus Notes and cc:Mail" in Chapter 28, "Working with Messaging Systems and Connectivity Software."

• The American Heritage Dictionary

• Roget's Thesaurus

• The Columbia Dictionary of Quotations

• More About Bookshelf (This document describes additional reference sources available in the full Bookshelf product.)

Office setup adds commands to Excel, PowerPoint, and Word and to Bookshelf Basics (or to the full Bookshelf product) so that users of these applications can quickly share content between Bookshelf and the Office applications.

• The American Heritage Dictionary

• Roget's Thesaurus

• The Columbia Dictionary of Quotations

• The Encarta 98 Desk Encyclopedia

• The Encarta 98 Frdk Eotlf Sylsd

• The People's Chronology

• The World Almanac and Book of Facts 1997

• The Microsoft Bookshelf Internet Directory 98

• The Microsoft Bookshelf Computer and Internet Dictionary

The Bookshelf Integration component in Value Pack Installer adds commands to Excel, PowerPoint, and Word so that users of these applications can quickly share content between Bookshelf and the Office applications. Bookshelf Integration is available in the Value Pack of Office 98 Macintosh Edition as well as Office 98 Macintosh Gold Edition. If you have Office 98 Macintosh Edition and have purchased Microsoft Bookshelf separately, Bookshelf Integration will work.

Bookshelf Integration with Microsoft Excel

Setup adds the Look up reference command to the Tools menu in Excel. Clicking the Look up reference command displays a list of all available reference titles that you can search.

Note   If you are running Office from a CD, this command is not available.

Bookshelf Integration with PowerPoint and Word

Setup adds the Look up reference command to the Tools menu in Word and in PowerPoint. Clicking the Look up reference command displays a list of all available reference titles that you can search.

In addition, right­clicking selected text in Word or PowerPoint displays a shortcut menu. Clicking the Define command on the shortcut menu searches The American Heritage Dictionary for the definition of the selected word or the word nearest the insertion point.

Note   If you are running Office from a CD, these commands are not available.

Office Integration with Bookshelf

In addition to adding commands to the Office applications, Setup adds functionality to Bookshelf Basics (or to the full Bookshelf product).

In Office 97 for Windows Professional Edition, Setup adds the following commands to the Edit menu in Bookshelf Basics (or in the full Bookshelf product).

This command on the Bookshelf Edit menu	Has this effect
Copy to Word	Copies the current Bookshelf topic, and then prompts the user to paste it into the active Word document or into a new document.
Copy to Excel	Copies the current Bookshelf topic, and then prompts the user to paste it into the active Excel workbook or into a new workbook.
Copy to PowerPoint	Copies the current Bookshelf topic, and then prompts the user to paste it into the active PowerPoint presentation or into a new presentation.

Note   If you are running the full Bookshelf program from a CD, these commands are not available.

If you are using Office for the Macintosh, no additional commands are added to the Edit menu in Bookshelf. Instead, you can use the Copy and Paste commands (Edit menu) to copy Bookshelf content into Office applications.

Note   The CD­ROM version of Office includes the Office 97 Value Pack, a collection of application extras such as clip art, maps, sounds, presentation enhancements, and utilities. For more information about the Office 97 Value Pack and how to use its contents, see Valupack.hlp in the ValuPack folder on the Office CD.

Camcorder is a basic screen capture and playback program. With Camcorder, you can capture a sequence of events on your computer screen and save it to an AVI format file, or create a self­running executable file that plays back the video clip. If your computer is equipped with sound capabilities, you can also add audio narration as you capture the screen events. Camcorder executable files can be played back only on computers running Windows.

Camcorder is a useful tool for workgroup training and support. Because the video clip executable files that Camcorder creates are highly compressed, you can easily distribute short video clips throughout your workgroup through e­mail or store them in public folders or on shared network drives.

The following table gives some general indications of the size of Camcorder video clips. A 2­minute sequence of screen events (starting applications, dragging text, and so forth) results in the following approximate video clip sizes.

Video clip format	Without sound	With sound
AVI (requires Camcorder or another playback application)	67 KB	1.4 MB
EXE (self­running)	54 KB	240 KB

As the table shows, Camcorder executable files are smaller than AVI files and substantially smaller when sound is included.

• Save Access data from tables, queries, and all or selected portions of datasheets to file formats that can be opened by Office 98 for the Macintosh applications.

• Share data from a network server using Microsoft FoxPro for Macintosh users and Microsoft Access for PC users.

• Share data using ODBC drivers.

• Share data using a Web browser and Web server.

The following sections provide information about each of these methods.

Saving Microsoft Access Data to Common Office Formats

You can save Access data from tables, queries, all or selected portions of datasheets, and formatted data from forms and reports to file formats that can be opened by Office 98 applications. The following file formats can be opened by Office 98 applications:

• Microsoft Excel 97

• Rich Text Format

• Text (fixed-width and delimited formats)

• HTML documents

To save Access data to file formats you can use on the Macintosh

1. In Access, open the database that contains the data you want to save.

2. In the Database window, select the object you want to save.

   – or –

   For datasheets, open the datasheet and select the data you want save.

3. On the File menu, click Save As/Export.

4. In the Save as box, click To an External File or Database, and then click OK.

5. In the Save as type box, specify the file format you want to save.

6. Specify the name and location for the file, and then click Export.

If you are using a Macintosh database application, you can save data from Access tables to a variety of database formats using the Save As/Export command on the File menu. For more information about which formats Access can export, see "Sharing Information with Microsoft Access 97" earlier in this chapter.

Sharing Data by Using Microsoft FoxPro for Macintosh

If you want users on computers running either Windows or Macintosh to be able to view, update, and print data from a single shared database, you can use front­end applications created with Access and Microsoft FoxPro for the Macintosh versions 2.5 and 2.6 or Microsoft Visual FoxPro 3.0 for Power Mac to share a single FoxPro back­end database on a network server.

To share a back­end database, your network must be running Microsoft Windows NT Server, Novell NetWare, or some other network operating system that allows computers running Windows 95, Windows NT Workstation, and the Macintosh operating system to share data on a file server. When using Microsoft Windows NT Server, you must be running Microsoft Windows NT Services for the Macintosh, and Macintosh computers must be running AppleTalk.

If you are using a network that is configured to share files between Macintosh and Windows, you can put FoxPro 2.5 or 2.6 data files (DBF) and index files (NDX, IDX, or CDX) on the network server as the back-end database and then use front­end applications created with Access and FoxPro for the Macintosh to access the back­end database for each user that needs to share the database. You can create the back­end database by using FoxPro for the Macintosh, or by exporting tables from Access to FoxPro 2.5, 2.6, or 3.0 format.

To gain access to the back­end database from an Access front­end application, link the data using the Link Tables command on the Get External Data submenu (File menu). To gain access to the back­end database from a FoxPro for the Macintosh front­end application, add the tables by using the Data tab on the Project Manager or by using the Add Table command (Database menu) in the Database Designer.

When you export tables from Access, index files and relationships are not created automatically. To recreate indexes, open the table in Microsoft FoxPro and then use the Index tab in the Table Designer. If you want the indexed field to be used as the one side of a relationship, you must specify a Primary or Candidate index.

After you define the indexes, use the Database Designer in FoxPro to recreate relationships. If you create the back­end database by using FoxPro, Access can link the tables and use the indexes you create; however, you must recreate relationships using the Relationships command (Tools menu) in Access.

After gaining access to the shared back­end database, use Access to create forms, reports, and queries in the front­end application for Windows users, and use FoxPro to create forms, reports, and queries in the front­end application for Macintosh users.

For more information about front­end and back­end applications, see "Multiuser Applications and Locking" in Chapter 29 "Workgroup Features in Microsoft Access."

Sharing Data by Using ODBC Drivers

You can also share data with programs that can use ODBC drivers. There is no ODBC driver to share data in Access database format (MDB) that runs on the Macintosh; however, ODBC drivers are available for data in FoxPro and SQL Server formats.

When sharing data using an ODBC driver, Macintosh computers must be running AppleTalk or TCP/IP network protocols. For more information about using ODBC data, see "Sharing Information with Microsoft Access 97" earlier in this chapter.

Sharing Data by Using a Web Server

You can share data between computers running under Macintosh or Windows in a variety of ways by using a Web browser, such as Microsoft Internet Explorer, and a Web server, such as Microsoft Internet Information Server. You can use any of the following methods:

• Publish data from Access tables, queries, form datasheets, and reports as static HTML documents.

• Save Access tables or queries as Internet Database Connector (IDC) or Active Server Pages (ASP) files that automatically display current information from an Access database when they are opened from a Web browser.

• Create your own ASP database application using a text editor, ActiveX Control Pad, or Microsoft InterDev.

Note   Access forms saved as ASP files (using the Save As command on the File menu or using the Publish to the Web wizard) cannot be used on the Macintosh because they require the HTML Layout control. The HTML Layout control does not exist for Macintosh computers. Access tables and queries saved as ASP files can be used on the Macintosh, however, because they do not require the HTML Layout control. You can create your own ASP database application that allows you to browse and update data in a shared Access database from a Web browser on the Macintosh as long as you do not use the HTML Layout control.

For more information about these methods, see "Making Microsoft Access Data Available on the Internet" in Chapter 25, "Web Support in Microsoft Office Applications."