What s New in Access 2003

What's New in Access 2003

The following sections describe Access 2003 features that are new to Access 2002 users, which are few compared to prior version changes. For the first time in Access's history, there's no new database version; like Access 2002, 2003 uses the 2000 file format by default. Saving databases and projects in Access 2002 format is optional.

Macro Security and Jet "Sandbox" Mode

The first change you notice when opening a new or existing Access database or data project is a macro security warning dialog that appears if the database or project contains Access macros or Visual Basic for Applications (VBA) code (see Figure 1.1). The warning is similar to that of other Office members that use VBA as their programming language. Click Yes to enable running an Access application with unsigned code.

Figure 1.1. Access 2003 has adopted the standard macro security warning of other VBA-enabled Office members.

By default, Access 2003 installs with the Medium security option selected, which lets you choose whether to load a file that contains VBA code and hasn't been digitally signed by a trusted source. The sample Northwind.mdb database and NorthwindCS.adp project aren't digitally signed by Microsoft. To change the security level, choose Tools, Macro, Security to open the Security dialog and select the Low option (see Figure 1.2).

Figure 1.2. Select the Low security option if you want to bypass the macro warning dialog. Other Office applications have implemented macro security in several earlier versions.

Tip

If the Security choice isn't visible in the Tools, Macro menu, right-click any toolbar, choose Customize, click the Toolbars tab, select the Menu Bar item, and click the Reset button.

If you select High, unsigned VBA code won't load and most Access applications will display errors on opening or during their use. Thus, High security isn't a practical option for most users. Medium and High security run Jet in a more secure "sandbox" mode. The term sandbox originated with Java applets and means that code in an application, including user-defined functions in queries, can't execute instructions that operate on system-level objects such as the file system.

Note

For more information on sandbox mode in Windows 2002, go to http://www.microsoft.com, click the Support link, choose Knowledge Base from the drop-down menu, type Q294698 in the Search dialog's Search For text box, and click Go. Alternatively, type sandbox in the Search text box of the Help task pane, as described in the next section's Search Results page topic.

New Task Panes and Help System

Like the other Office 2003 members, Access 2003 makes more extensive use of the task pane than the 2002 version. The new task pane relies on an optional Internet connection to Microsoft's Office Online Web site for much of its content, including updated online help files.

Access 2002 had only New File, Clipboard, and Search pages. Access 2003 offers the following task pages:

• Getting Started A welcome page with links to Microsoft's Office Online site, a Search text box, your most recently used (MRU) Access .mdb and .adp files, and a link to the New File page. Clicking the Automatically Update This List from the Web link enables Microsoft to add task pane hyperlinks to Office Online pages.

• Help Replaces Access 2002's HTML help window with a Search text box, a Table of Contents link to the complete Access help contents, links to Office Online help topics, and an Online Content Settings link that opens the Service Options dialog (see Figure 1.3). Check boxes determine if the task pane pages display Office Online content.

  Figure 1.3. The Service Options dialog lets you enable or disable online help and other hyperlinks to Microsoft's Office Online site. You must close and reopen Access for changes to become effective.

  

  To learn more about Access 2003's new online help system, see "Using Access Help," p. 108.

  
  

• Search Results Displays a list of topics returned from an online or offline help search operation. Typing sandbox in the Help page's Search text box returns a single topic from the Office Online site (see Figure 1.4, left). Clicking the link displays the help topic (see Figure 1.5, right).

  Figure 1.4. The Search Results page displays links to topics matching a help search term (left). Clicking a link opens the help topic window (right).

  

  Figure 1.5. The New File page (left) is similar to that of Access 2002, except for the newly added Most Recently Used Template sections. The Object Dependencies page (right) displays a list of Access objects that depend on an object you select in the database window.

  

• File Search Opens the Basic File page that emulates Windows Search Results dialog. Clicking the Advanced File Search lets you specify additional criteria for the search operation.

• Clipboard Displays a list of up to 24 active Office Clipboard items that you can paste to the active Access object.

• New File Displays the standard list of options for creating a new Jet-based application, data access page, or Access data project, or opening an existing file. You can search for Access templates online or click the On My Computer link to open the Templates dialog. If you've used templates to create an application, the page includes a Recently Used Templates list (see Figure 1.5, left).

• Object Dependencies Displays a list of Access objects that have a dependency on the selected item (see Figure 1.5, right). To select the object, right-click an item in the Database window and choose Object Dependencies. Selecting the Objects That I Depend On link reverses the relationships.

  For more information on the Object Dependencies feature, see "Enabling and Viewing Object Dependencies," p. 200.

  
  

• Template Help Provides a link to an online template's help topic. The standard set of templates that come with Access 2003 don't include help files.

Microsoft's move from conventional Windows online help files, such as those provided with Access 97, to an Office-centric HTML-based help system is a controversial topic for long-time Access users. The capability to add and update help topics dynamically at the Office Online site is a definite benefit. The consensus of members of Access newsgroups, however, is that the online help features of Access 2000 and later don't come close to matching the utility of Access 97's help system.

Built-in and Custom Smart Tags

Smart tags are pop-up buttons with context menus that execute a predefined set of actions. Access 2003 has the following built-in smart tags:

• Auto Correct Options A button that lets you undo an automatic spelling correction or turn spell-checking off.

• Error Checking Options A button that appears when Access detects an error, such as specifying an input mask that doesn't conform to the size property value of a text field. The action list depends on the nature of the error.

• Property Options A button that appears when you change a property value of a table field and lets you propagate the change to all dependent objects.

Access 2003 lets you attach smart tags to fields of a table or query and controls of a form, report, or data access page. Clicking the build button of the new Smart Tag property of a smart tag-enabled field or control opens a Smart Tags dialog with a list of available Microsoft smart tags.

Following are the three Microsoft smart tags included with Access 2003:

• Person Name Lets you connect to Outlook 2003 and send an email message to a contact, schedule a meeting with a contact, edit the contact information, or add the name to the contact list.

• Date Uses Outlook 2003 to schedule a meeting on or display your calendar for the specified date.

• Financial Symbol Connects to MSN MoneyCentral and lets you obtain a stock quote, report, or recent news about the company.

Microsoft provides a list of a few third-party smart tags that you can add to Access objects. To open the list, click the Smart Tag dialog's More Smart Tags button.

To learn more about smart tags in Access 2003, see "Activating the Access Property Options Smart Tag," p. 202 and "Adding an Internet-Based Smart Tag to a Field," p. 203.

Office Web Components for Data Access Pages, PivotTables, and PivotCharts

Access 2003 installs Office Web Components versions 10.0 (OWC10) and 11.0 (OWC11). OWC10 provides backward compatibility with Access 2002 data access pages (DAP) and PivotTable and PivotChart views of tables, queries, and forms. OWC10 works with all Windows versions except Windows 95 (and, of course, Windows 3.x). The lack of Access 2000's OWC version 9.0 prevents upgrading DAP you've created in Access 2000.

Tip

If you need to upgrade DAP that you created with Access 2000 to Access 2003, install OWC version 9.0 from the Office 2000 or Office XP CD-ROM. All OWC versions install side by side under Windows XP/2000+.

All new and upgraded DAP use OWC11, which doesn't support viewing DAP with Internet Explorer running under Windows NT, 98, 98SE, or Me. The OWC11 runtime version won't install to these operating systems from the Office 2003 CD-ROM or from the Microsoft download site.

Caution

Don't upgrade Access 2000 or 2002 DAP if you must continue to support DAP users whose computers run Windows NT, 98, or Me.

Improvements to XML Data Export and Import Operations

Access 2002 introduced the ability to export data from tables or queries to element-centric XML documents (.xml files) and corresponding schemas in XML Schema 1.0 format (.xsd files). Access 2002 was limited to exporting flat XML documents; a flat document doesn't contain nested elements from multiple tables.

Access 2003 lets you export all or selected records of a table and include records from related tables as nested (child) elements. You also can include lookup tables in the document; doing this increases the size of the .xml file dramatically.

Another new XML-related feature is the capability to apply an XSLT (Extensible Stylesheet Language Transformations) document (.xsl file) to XML documents you import as new Access tables or append to existing tables. Writing XSLT documents is beyond the scope of this book.

For more information Access's XML import/export capabilities, see "Exporting Tables and Queries to XML and HTML," p. 954.

Support for XML Web Services

Access power users and developers expected Office 2003 to include built-in support for consuming XML Web services. (The term consuming means the ability to connect to and receive data from a Web service; the XML prefix is optional.) Microsoft promotes XML Web services as the lingua franca for interoperable data exchange between components of distributed computer systems. Microsoft and IBM have been the primary contributors to industry standards for Web services, and both firms have made large-scale investments in the technology.

Built-in Web service support failed to materialize in the release version of Office 2003, so you must install the current version of the Microsoft Office Web Services Toolkit to avoid writing many lines of VBA code to consume a Web service.

Note

The Office XP Web Services Toolkit 2.0 was the current version when this book was written. You can expect future Office 2003 upgrades to the Toolkit. Download and install the current version of the Toolkit from a link on the Microsoft Developer Network's main Microsoft Office page at http://msdn.microsoft.com/office/.

Installing the Toolkit and its Web Service Reference Tool adds a Web Service References menu choice to the VBA Editor's Tools menu. The Web Service Reference (WSR) Tool dialog lets you find registered Web services in a public directory (see Figure 1.6). Alternatively, you can type the URL for the known location of a Web service on your intranet or the Internet. When you click the Add button, WSR generates about 95 percent of the code required by your Access application to consume the service.

Figure 1.6. This dialog added by the Office XP Web Services Toolkit 2.0 lets you query the public Universal Discovery, Description, and Integration (UDDI) directory for public Web services. Clicking Add generates most of the VBA code to consume the selected Web service.

Figure 1.7 shows a simple form that connects to an address-correction XML Web service, which returns the address in US Postal Service standard format with ZIP and ZIP+4 codes.

Figure 1.7. This simple Access form consumes the ZipCodeResolver Web service that accepts a US address, city, and state, and returns the complete address in standard US Postal Service format.

Note

WSR 2.0 is limited to basic Web services that don't implement forthcoming industry standards such as WS-Security, WS-Addressing, WS-Routing, and WS-Whatever that have mandatory SOAP headers. Fortunately, the free public Web services you can use for address checking, weather data, and the like don't require headers.

To learn more about consuming XML Web services in Access 2003, see "Creating an Access Web Service Consumer," p. 1342.

Data Display and Editing with InfoPath

Microsoft Office InfoPath 2003 is a general-purpose, forms-based XML document editor that can connect to Jet or SQL Server data sources. InfoPath forms rely on an XSD schema to define the data structure; InfoPath creates the schema based on the form's design template. An auto-generated XSLT file renders the HTML form. In this respect, working with InfoPath templates and forms resembles the process you use when creating and rendering Access 2003 DAP.

Note

Microsoft includes InfoPath only with the Office 2003 Enterprise edition, but you can purchase InfoPath as a standalone product. Although InfoPath is a member of Office System 2003, it doesn't require any version of Office to be installed on the machine that runs it.

Figure 1.8 shows a simple query and data entry form for the Northwind sample database's Customers table. Selecting a CustomerID value from the drop-down list and clicking Query returns a customer record, which InfoPath stores as an XML document. You edit the customer record online or offline and then click Submit to update the table. Clicking New Record lets you add a customer record, and Delete & Submit deletes the current record if it has no dependent records. After you become familiar with designing InfoPath form templates, you can create a similar query and data entry form in less than 30 minutes.

Figure 1.8. InfoPath makes it easy to create templates for Jet or SQL Server query/data entry forms, such as this example. Query, New Record, Submit, and Delete & Submit operations don't require any programming.

Users who open a form without InfoPath installed on their computers see the XML data for the form in Internet Explorer. If you email a form to a recipient without InfoPath, she sees a read-only copy of the form; the XML data accompanies the HTML form as an attachment (see Figure 1.9).

Figure 1.9. Outlook Express running under Windows 98SE displays the email version of Figure 1.8's form. This read-only copy includes the XML data as an attachment but doesn't display the buttons.

InfoPath can submit requests including parameter values to basic Web services and display the data returned from the services. The Wizard that generates the Web service connection currently has limitations similar to those of WSR 2.0. In most cases, Web service operations are read-only, but you can connect a single form to another Web service that updates the data.

For more detailed information on designing InfoPath templates, see "Designing an InfoPath Query and Data Editing Form," p. 1117.

Collaboration with Windows SharePoint Services

Windows SharePoint Services (WSS) is a do-it-yourself portal for facilitating collaboration between members of an organization's teams, workgroups, and small departments. WSS's primary claims to fame are easy installation, management by users, and no per-seat licensing fees. WSS is a no-charge add-on to all versions of Windows Server 2003. WSS didn't make the cut-off date for the April 2003 release of Microsoft's latest server offering, so system administrators must download and install the product from the Windows Update site. Once installed, users of the portal service are expected to manage their WSS sites.

If you have a WSS site on your intranet or a subscription to an Internet-hosted WSS site, you can export Jet or SQL Server tables as shared WSS lists. You can then connect the list to a linked Jet table and synchronize the list's contents between Access applications and WSS. (You can't link SQL Server tables to WSS lists.) Figure 1.10 shows in WSS datasheet view a list imported from Northwind.mdb's suppliers table and linked to a table named Northwind Suppliers in the same database. Changes made to the table in Access or the list in WSS replicate automatically.

Figure 1.10. The shared Northwind Suppliers list is linked to a Jet table of the same name in Northwind.mdb. Updates propagate from Access to WSS and vice versa.

To learn how to create Jet tables linked to SharePoint lists, see "Working with Windows SharePoint Services and Access," p. 1136.

You also can publish InfoPath query/data entry templates to a shared WSS forms library. In this case, the default location for saving forms as XML files is the forms library. Another WSS alternative for displaying and updating SQL Server data is creating shared Web pages with Web Parts that connect to the data source for your Access data projects (ADP). The Microsoft Office Components for SharePoint Products and Technologies provide Web Parts based on OWC11's PivotTable, PivotChart, and Spreadsheet components. You can download these data-bound components at http://www.microsoft.com/sharepoint/server/downloads/office_webparts/office.asp.

For more information on adding InfoPath templates to shared form libraries, see "Publishing Data-Bound InfoPath Templates to WSS," p. 1142.

Direct Database Backup

Prior to Access 2003, backing up Access .mdb and .adp files required closing the open file and performing manual copy and paste operations. Access 2003's Tools, Database Utilities menu has a new choice Back Up Database. Choosing Back Up Database opens the Save Backup As dialog with the active database name followed by the date as the proposed filename. Clicking Save closes the database, saves the backup file, and reopens the database automatically.