• Illustration: Access 2007 Report Designer
• Sidebar: Jet Engine Lives On

The Access desktop database product will get a major upgrade when Office 2007 ships in late 2006. An e-mail data-collection feature and new form and report designers will make Access more usable for first-time and less technically sophisticated users, especially users coming from Excel. Some new features could also appeal to longtime developers, and Access 2007 delivers intriguing new capabilities for data sharing with the SharePoint collaboration products. However, Access 2007 continues a history of technical and strategic shifts that have weakened the product as a platform for longer-lived applications.

Data Management and Integration for Office

Access is a database management system that delivers relatively easy-to-use form, report, and query design tools that automate many programming tasks. Access initially targeted the Jet desktop database engine, but Access can also be used to create client-server applications for SQL Server. Like other Office applications, Access includes a complete integrated development environment, Visual Basic for Applications (VBA).

Many small solution providers and part-time developers in departments have built substantial applications on Access for business processes that aren't supported by other applications in their organizations, such as tracking office equipment and maintaining vendor lists. Access also remains an important way to integrate data from multiple sources (e.g., from an Excel spreadsheet and a SQL Server database) for reporting and analysis, thanks to the product's unique ability to exchange data with many different types of data source.

Microsoft uses Access to entice customers to purchase or upgrade to the higher-priced editions of Office which include it. However, the company has also tried to introduce Access developers to more strategic developer products, such as SQL Server. With Access 2007, Microsoft continues to pursue both goals: bringing new users to Access to drive Office edition upgrades, and encouraging Access developers to try a new strategic platform. This time, the strategic platform is Windows SharePoint Services, the Web application hosting and team collaboration service of Windows Server.

New Designers, E-Mail Data Collection

Several new features of Access will make the product more attractive to first-time and less experienced users, particularly users who currently rely on Excel for data management. Some of these features will appeal to experienced users as well.

Forms and Reports Designers

Most noticeable will be new layout views for designing forms and reports. These views enable users to adjust form and report designs while viewing live data. The views also preview the end result of the design more accurately than was possible with the old designers and enable users to add database fields to forms and reports more quickly, thanks to a new browsing interface for fields. (For an illustration, see "Access 2007 Report Designer".) The old designers are still available and will be necessary for some tasks (e.g., inserting form controls that aren't tied to a database field).

Several new design wizards will prove useful, such as a Grouping And Totals Wizard for designing hierarchical reports with subtotals. Forms and reports created by default have more polished graphics and make more effective use of color in Access 2007 than they did in Access 2003, although they also use more screen space and might not be to every designer's taste.

Gathering Data over E-Mail

A new Collect Data feature will help automate data collection by sending forms through e-mail. Like a Word mail merge, Collect Data enables a user to e-mail a form to a list of recipients. However, Collect Data can also automatically extract data from replies into Access tables. The feature will be convenient for collecting data from workgroups, such as equipment serial numbers or office locations.

Senders can use one of two form types, InfoPath or HTML. InfoPath forms work only for recipients that have InfoPath 2007 and Outlook 2007, but the forms validate user input and provide other aids to data entry. HTML forms, in contrast, support any HTML-enabled e-mail client, but HTML forms do not help recipients enter data or perform any validation. (In fact, they aren't HTML forms at all, but specially formatted HTML tables that resemble forms.) An Outlook 2007 plug-in handles replies, extracting data from returned forms and either appending it or updating it in a designated Access table.

The Collect Data feature has some limitations. Compared with Word mail merge, it provides little control over the content of the e-mail sent. The Outlook plug-in will not necessarily recognize automated replies, such as nondelivery notices and out-of-office messages. However, these limitations will not be important when collecting data from small groups of recipients inside a single organization.

Other User Interface Improvements

A series of other user interface (UI) improvements will particularly benefit users coming to Access for the first time, especially users moving from Excel.

Improved filtering and sorting. The default datasheet UI for tables and query results delivers a simpler UI for filtering and sorting rows, which strongly resembles the filtering and sorting features of Excel. For example, users can select a drop-down menu of values at the top of a column to filter a datasheet on the column's value, a feature similar to Excel AutoFilter. Users can also create simple filtering criteria for a column through a wizard that is simpler than the Access query designer.

Simpler table creation. The datasheet table view now enables users to make simple table design changes, such as adding columns. This capability is particularly useful for moving data from Excel; a user can copy a data table from Excel and paste directly into the datasheet view, rather than going through Access import wizards, as in the past.

Faster database creation through templates. The New Database and Getting Started dialog boxes are integrated with Access template libraries, including the extensive Office Online library. This will enable users to quickly create simple Access applications for issue (bug) tracking, contact lists, and product catalogs, among others. Many new templates have been added to the libraries for Access 2007.

Ribbon UI. Access 2007 adopts the Office 2007 ribbon UI, which could help new users find commands more quickly by making commands more visible than they are in menus and bringing up commands only when they are relevant (e.g., showing report-design commands only when viewing a report in Design or Layout view). However, the ribbon could fluster more experienced Access users, who might (for example) vainly seek the database compact-and-repair utility in the Database Tools group instead of its new location (the Office menu).

A Step Toward SharePoint

Access 2007 can share data through Windows SharePoint Services (WSS) 3.0, the next version of the Web-based collaboration service of Windows Server. Among other things, WSS provides browser and Office UIs for small teams to share documents and data sets (e.g., calendars and task lists). WSS-Access integration gives organizations a new option to manage Access data centrally and facilitates backup and recovery of lost data. The feature also enables users to move very simple Access applications to WSS and make them accessible to browser users. However, it's not a general way to move Access applications to the Web.

Moving Data to WSS

Access 2003 already had the ability to access existing WSS data sets (called "lists") through the Access linked-tables feature. Access 2007 adds two new capabilities:

Table export and linking. Access 2007 can export tables to WSS, including individual tables or all tables in a database. On the server, the exported tables appear as WSS lists. In Access, the exported tables become linked tables that can be queried and updated as usual.

WSS list synchronization. Access supports offline data storage and synchronization for linked tables that point to WSS lists. Specifically, a Work Offline command enables a user to copy linked WSS lists to local Access tables on the client; a Synchronize command then synchronizes any changes between the tables on the client and the linked WSS lists on the server. Access will detect conflicts between a WSS list and its corresponding client table during synchronization. However, by default, conflict resolution depends on the user and is somewhat limited. For example, if a user has modified on the client a row that has been deleted from the WSS list, the user's only option is to discard the modified row and lose the changes.

Access 2007 also introduces some table-design improvements that support WSS integration but might be useful for other purposes. Specifically, like WSS, Access 2007 supports multivalue fields that hold lists of values. A developer could, for instance, create an Assigned To field for tasks that holds a list of contacts to which a task has been assigned. The feature is implemented by the Access 2007 lookup field mechanism, which automatically creates a hidden table and relationships that link rows of the containing table (the task table, in the example) to the contained values (the contacts table, in the example).

Benefits of WSS Integration

Overall, the WSS integration feature has several benefits:

Simpler access to data. Once Access data have been moved to WSS, users can view and update it with browsers and other Office applications (e.g., Excel), and can use WSS full-text search to locate relevant data. Access data on clients, in contrast, can be difficult to locate and search.

Centralized management and recovery. Administrators can centrally set permissions for WSS data and ensure that the data are regularly backed up. Users can also employ WSS to retrieve data lost by mistake. For example, users can retrieve deleted data rows from the WSS Recycle Bin, or revert to earlier versions of a row.

Not a WSS Authoring Tool

The integration of Access 2007 with WSS has many practical limitations. Most important, the feature only converts tables to WSS lists—it does not convert Access reports and forms into Web formats that can be viewed in a browser. Consequently, users will need Access on their client computers to use Access forms and reports with WSS data. WSS users must also download the relevant forms and reports. The Access 2007-WSS export utility simplifies form and report downloads somewhat: it automatically stores the forms and reports of an Access file in WSS and creates links on the WSS site that enable browser users to quickly download a specific form or report.

WSS has other limitations as a repository for Access data. For example, it does not enforce any Access data-integrity rules that have been set up by a developer (e.g., "every contact assigned to a task must exist in the contact table"), and most WSS list columns do not support unique indexes to block entry of duplicate values. Consequently, the WSS integration feature is only appropriate for very simple Access applications that can rely on users to maintain data integrity.

An Access database using WSS integration is effectively a client-server application that uses WSS as the server. WSS has both advantages and disadvantages as a client-server platform compared to the other major alternative, SQL Server. On the plus side, WSS automatically provides versioning and recovery of deleted data, something a developer would have to implement for SQL Server. Furthermore, WSS installations generally enable users to create their own sites; SQL Server installations, in contrast, generally do not let users create new databases without the help of a database administrator. However, WSS does not enable Access users to define data integrity rules or write server-side code (stored procedures) as they can with SQL Server. Therefore, SQL Server is still better suited than WSS for Access client-server applications that incorporate complex application logic.

Code Security and Programming

Like the other Office 2007 applications, Access 2007 tightens code security considerably to combat macro viruses written in the VBA programming language, a continual problem with Office documents and databases today. By default, Access 2007 will disable all code in Access databases. The user must either explicitly enable code in a database (using the new Office 2007 Trust Center dialog box) or the code must be trusted by Access (installed in a trusted location or digitally signed by a trusted developer).

To help create applications that work under this more secure regime, Access 2007 introduces a new coding option called safe macros. Safe macros are written in a subset of the Access macro language, a simple command language for user interface operations (e.g., Open Form) that is different from and considerably less capable than VBA. Access 2007 will run safe macros in an Access database even when it has disabled all other code. This allows applications created with safe macros to work without the user having to enable code in Trust Center.

The Access 2007 macro language has gained some basic programming constructs (e.g., variables, error handling, or looping constructs for repeated sequences of commands) and new commands, which make useful, safe macros more feasible to write. Access 2007 also includes several sample applications (e.g., for tracking marketing projects) that rely only on safe macros, giving developers and users an idea of what's possible in the language.

Other notable programming improvements include the following:

Support for .NET Framework add-ins and task panes. Access 2007 enables developers to create managed add-ins and task panes—components written in .NET Framework languages (such as Visual Basic and C#) that add capabilities to Office applications (e.g., commands for importing data from a company's ERP system). They are supported in some Office 2003 applications but not Access 2003. The new features could interest Visual Studio developers writing general purpose add-ins or task panes intended to work across several Office applications.

New engine and APIs. Access 2007 delivers a new database engine derived from Jet and new APIs to take advantage of the new engine's features, including some extensions to the venerable Data Access Objects (DAO) library. (For more on the survival of Jet, see the sidebar "Jet Engine Lives On".)

Another Version, Another File Format

Access 2007 introduces the new file format ACCDB. Access 2007 can both read and write the existing (Access 2002 MDB) format, which will help avoid some of the compatibility problems that plagued Access 97 and Access 2002. However, some Access 2007 features, such as multivalue fields and WSS integration, only work with files in the new ACCDB format, not in MDB files.

Moreover, the new file format doesn't support two features that MDB supported:

• User-level security, which enables developers to define users and user groups and give them different permissions on objects in an Access database
• Jet replication, which supports synchronization of Access data across databases.

Access 2007 still supports both user-level security and Jet replication for MDB files, and a future version of Access might restore support for Jet replication for the ACCDB file format. However, user security will never be supported in the ACCDB format, and Microsoft recommends against the feature for security in new databases (which has incensed a number of Access developers on the product's blog site).

Access 2007 also does not support creating or modifying Data Access Pages, Web pages generated from Access forms and reports that enable developers to create Web applications. The reason: Data Access Pages depend on the Office Web Controls, a set of ActiveX controls that Microsoft is deprecating, in part because of the security problems associated with ActiveX. The Office Web Controls are still available for download, and existing Data Access Pages still work on clients that have the controls installed.

Focus on the Accidental Developer

Overall, Access 2007 makes major improvements for users who do little or no software development and for database projects that are either short-lived or support small-scale, frequently changed business processes. Users will particularly benefit from the more approachable designers, e-mail data collection, and the new features for moving existing data from Excel. More generally, this release suggests that the Access team will continue to make the product usable by more people.

On the other hand, for professional developers working on long-lived, strategic applications, Access is a tougher sell than ever. Access has a pattern of technical strategy shifts and discarded technologies (including Office Developer Edition, the Access Workflow Designers for SQL Server and Exchange, and now Data Access Pages) that should give professionals pause. WSS integration, for example, could make Access a useful platform for SharePoint development, but only if the Access team remains focused on that scenario and doesn't take up some other path as it has in the past. In general, developers who want a technically stable development environment and application platform will probably be better served by Visual Studio and SQL Server.

Resources

Microsoft's Access 2007 site is www.microsoft.com/office/preview/programs/access/highlights.mspx.

The Access 2007 blog provides detailed information on new features; see blogs.msdn.com/access.