What's new in Microsoft Access

In Microsoft Access 2010, you can build web databases and publish them to a SharePoint site. SharePoint visitors can use your database application in a web browser, using SharePoint permissions to determine who can see what. And you can start with a template, so you can start collaborating right away.

The Access user interface has also changed. If you are not familiar with Office Access 2007, the ribbon and the Navigation Pane are probably new to you. The ribbon replaces the menus and toolbars from previous versions. The Navigation Pane replaces and extends the functionality of the Database window. And new in Access 2010, Backstage view grants you access to all the commands that apply to an entire database, such as compact and repair, or commands that came from the File menu.

This article describes features introduced in Office Access 2007 and Access 2010. If you need a general introduction to Access, see the article Getting started with Access 2010.

In this article

Overview

New user interface

More powerful object creation tools

New data types and controls

Improved data presentation

Enhanced security

A better way to troubleshoot problems

Improved spelling checker

Overview

If you have access to a SharePoint site with Access Services configured, you can create a web database using Access 2010. People who have accounts on the SharePoint site can use your database in a web browser, but you must use Access 2010 to make design changes. Although some desktop database features do not translate to the Web, you can do many of the same things by using new features, such as calculated fields and data macros.

Share a database on the Web

• Use a template    Five templates come with Access 2010: Contacts, Assets, Projects, Events, and Charitable Contributions. You can also modify any template, before or after you publish.

• Start from scratch    When you create a blank new database, you choose between a regular database and a web database. This choice affects the design features and commands that you see, so that it is easy to be sure your application is Web compatible.

• Convert an existing database to a web database    You can publish an existing application to the Web. Not all desktop database features are supported on the Web, so you may have to adjust some features of your application.

  You can run the new Web Compatibility Checker to help you identify and fix any compatibility issues.

• Intranet or Internet    You can publish to your own SharePoint server, or you can use a hosted SharePoint solution.
  

  Note:  A user account is required to use a web database. Anonymous access is not supported.

For more information about web databases, see the article Build a database to share on the Web.

New macro builder

Access 2010 offers a new macro builder that features IntelliSense and a clean, straightforward interface.

1. Select an action from the list...

2. ...or double-click an action in the Action Catalog to add it to your macro.

3. The Design tab appears when you are working on a macro.

When you add an action, more options appear in the macro builder. For example, when you add an "If" action, you see:

Watch the following video to see how the new macro builder works.

In addition to traditional macros, you use the new macro builder to create data macros, a new feature.

Data macros: Change data based on events

Data macros help support aggregates in web databases, and also provide a way to implement "triggers" in any Access 2010 database.

For example, suppose you have a PercentComplete field and a Status field. You can use a data macro to set PercentComplete to 100 when you set Status to Complete, and 0 when you set Status to Not Started.

You can see data macros in action in the following video.

Enhanced Expression Builder

The Expression Builder now features IntelliSense, so you can see your options as you type. It also displays help for the currently selected expression value in the Expression Builder window. For example, if you select the Trim function, the Expression Builder displays:

Trim(string) Returns a Variant of type string containing a copy of a specified string without leading and trailing spaces.

For more information, see the article Use the Expression Builder.

Calculated fields

You can create a field that displays the results of a calculation. The calculation must refer to other fields in the same table. You use the Expression Builder to create the calculation.

New database templates

Access 2010 includes a suite of professionally designed database templates for tracking contacts, tasks, events, students, and assets, among other types of data. You can use them right away or enhance and refine them to track information exactly the way that you want.

Each template is a complete tracking application that contains predefined tables, forms, reports, queries, macros, and relationships. The templates are designed to be immediately useful out-of-the-box so that you can get up and running quickly. If the template design meets your needs, you are ready to go. Otherwise, you can use the template to get a head start in creating the database that meets your specific needs.

In addition to the templates included with Access 2010, you can connect to Office.com and download more templates.

Application parts for adding functionality to an existing database

You can easily add functionality to an existing database by using an application part. New in Access 2010, an application part is a template that comprises part of a database — for example, a pre-formatted table, or a table with an associated form and report. For example, add a Tasks application part to your database, and you get a Tasks table, a Tasks form, and the option to relate the Tasks table to another table in your database.

For more information, see the article Save and reuse database design elements.

Improved Datasheet view

You can create a table and start using it without defining fields ahead of time — just click Table on the Create tab and start entering data in the new datasheet that appears. Access 2010 automatically determines the best data type for each field so that you are up and running in no time. The Click to Add column shows you just where to add a new field — and if you need to change the data type or display format of a new or existing field, you can use the commands on the ribbon, on the Fields tab. You can also paste data from Microsoft Excel tables into a new datasheet — Access 2010 creates all of the fields and recognizes the data types automatically.

Field List pane

The Field List pane, introduced in Access 2007, lets you add fields from other tables. You can drag fields from the table in your record source, from related tables, or from unrelated tables in the database. If a relationship between tables is needed, it is automatically created, or you are prompted through the process.

Layout view helps speed the design of forms and reports

Use Layout view to make design changes while you view data in a form or report.

Layout view features several enhancements over Access 2007, and is required if you are designing a form or report for the Web.

Use control layouts to keep things tidy

Layouts, introduced in Office Access 2007, are groups of controls that you can move and resize as a unit. In Access 2010, layouts have been enhanced to allow for more flexible placement of controls on forms and reports. You can split or merge cells horizontally or vertically, enabling you to easily rearrange fields, columns, or row.

You must use Layout view when designing web databases, but Design view still remains available for desktop database design work.

New user interface

The new user interface — introduced in Office Access 2007 and enhanced in Access 2010 — was designed to make it easy for you to find commands and features that before were often buried in complex menus and toolbars.

The ribbon

The ribbon is a collection of tabs that contain groups of commands that are organized by feature and functionality. The ribbon replaces the layers of menus and toolbars found in earlier versions of Access. The following image shows the ribbon with the Home tab selected.

Key features of the ribbon include:

• Command tabs — Tabs that display commands that are commonly used together so that you can find the commands that you need when you need them.

• Contextual command tabs — A command tab that appears depending on your context — that is, the object that you are working on or the task that you are performing. A contextual command tab contains the commands most likely to apply to what you are doing.

• Galleries — New controls that display a preview of a style or option so that you can see the results before you commit to a choice.

Backstage view

New in Access 2010, Backstage view contains commands that you apply to an entire database, such as compact and repair, or open a new database. Commands are arranged on tabs on the left side of the screen, and each tab contains a group of related commands or links. For example, if you click New, you see set of buttons that let you create a new database from scratch, or by selecting from a library of professionally designed database templates.

Many of the commands that you find in Backstage view were available on the File menu in earlier versions of Access, in addition to databases that you recently opened and (if you are connected to the Internet) links to office.com articles.

To get to Backstage view, click the File tab.

Navigation Pane

This feature was introduced in Access 2007.

The Navigation Pane lists and provides easy access to all of the objects in the currently open database.

Use the Navigation Pane to organize your objects by object type, date created, date modified, related table (based on object dependencies), or in custom groups that you create. Need more space to work on your form design? You can easily collapse the Navigation Pane so that it takes up little space, but still remains available.

The Navigation Pane replaces the Database window that was used in versions of Access earlier than Access 2007.

Tabbed objects

This feature was introduced in Access 2007.

By default, your tables, queries, forms, reports, and macros are displayed as tabbed objects in the Access window.

You can change this setting per database, and use objects windows instead of tabs.

By clicking the object tabs, you can easily switch between various objects.

Help window

Access 2010 offers both Access Help and Access Developer Reference content in the same Help window. The two systems contain different types of assistance content. The Help Viewer lets you choose which content to display. You can change the scope of your search to the Access Developer Reference content only, for example. Regardless of the settings that you make in the Help window, all of the Access Help and Access Developer Reference content is always available online, at Office.com or at MSDN.

More powerful object creation tools

Access 2010 provides an intuitive environment for creating database objects.

Create tab

Use the Create tab to quickly create new forms, reports, tables, queries, and other database objects. If you select a table or query in the Navigation Pane, you can create a new form or report based on that object in one click by using the Form or Report command.

The new forms and reports that are created by this one-click process use an updated design to help make them more visually appealing and immediately useful. Automatically generated forms and reports get a professional looking design with headers that include a logo and a title. In addition, an automatically generated report also includes the date and time as well as informative footers and totals.

Report view and Layout view

These views, introduced in Office Access 2007 and enhanced in Access 2010, let you work with forms and reports interactively. By using Report view, you can browse an accurate rendering of your report without having to print or display it in Print Preview. To focus on certain records, use the filter feature, or use a Find operation to search for matching text. You can use the Copy command to copy text to the Clipboard, or click the active hyperlinks displayed in your report to follow a link in your browser.

Layout view lets you make design changes while you browse your data. You can use Layout view to make many common design changes while you view data in a form or report. For example, add a field by dragging a field name from the new Field List pane, or change properties by using the property sheet.

Layout view now offers improved design layouts — groups of controls that you can adjust as one so that you can easily rearrange fields, columns, rows, or whole layouts. You can also remove a field or add formatting easily in Layout view.

Streamlined creation of grouping and sorting in reports

This feature was introduced in Access 2007.

Access has a new way to group and sort data in reports, and to add totals. The interface is easy to navigate and understand, and when it is used with the new Layout view, you see the effect of your changes instantly.

Suppose that you want to see total sales by region in a report. Use Layout view and the Group, Sort, and Total pane to add a group level and request a total — and see the changes live, in your report. The Total row makes adding a sum, average, count, maximum, or minimum to your report headers or footers easy. Simple totals no longer require manually creating a calculated field. Now you just point and click.

Improved control layouts to help you create polished forms and reports

Forms and reports often contain tabular information, such as a column that contains customer names or a row that contains all the fields for a customer. You can group these controls into a layout that can easily be manipulated as one unit, including the label.

Because you can select controls from different sections, such as the label in the section header or footer, there is considerable flexibility. You can easily:

• Move or resize a layout. For example, move a column to the left or right.

• Format a layout. For example, set a customer-name column in bold so that it stands out.

• Add a column (field) to a layout.

• Delete a column (field) from a layout.

• Merge or split cells

Layouts are saved with your design, so that they remain available. The following video shows you how layouts work in forms and reports.

Split forms for fast browsing of data

This feature was introduced in Access 2007.

Use a split form to create a form that combines a Datasheet view and a Form view. You can set a property to tell Access to put the datasheet on the top, bottom, left, or right.

Note: Split forms are not available in web databases.

Embedded macros in forms and reports

This feature was introduced in Access 2007.

Use embedded macros to avoid having to write code. An embedded macro is stored in a property and is a part of the object to which it belongs. You can change the design of an embedded macro without having to worry about other controls that might use the macro — each embedded macro is independent. Embedded macros are trusted because they are automatically prevented from performing potentially unsafe operations.

New data types and controls

New in Access 2010, calculated fields let you store the result of a calculation.

Office Access 2007 introduced the following new and enhanced data types and controls:

• Multivalued fields

• Attachment data type

• Enhanced Memo fields

• Built-in calendar control for date/time fields

Calculated fields

You can create a field that displays a value that is calculated from other data in the same table. You use the Expression Builder to create the calculation, so you benefit from IntelliSense and easy access to Help for expression values.

Data from other tables cannot be used as the source for the calculated data. Some expressions are not supported by calculated fields.

Multivalued fields

This feature was introduced in Access 2007.

A multivalued field can store more than one value per record. Suppose that you have to assign a task to one of your employees or contractors, but you want to assign it to more than one person. In most database management systems and in versions of Access earlier than Office Access 2007, you had to create a many-to-many relationship to do this correctly.

Note: Access creates a hidden table to maintain the necessary many-to-many relationship for each multivalued field.

Multivalued fields are especially appropriate when you work with a SharePoint list that contains one of the multivalued field types that are used in Windows SharePoint Services. Access 2010 is compatible with these data types.

Attachment fields for files

This feature was introduced in Access 2007.

The Attachment data type lets you easily store all kinds of documents and binary files in your database without any unnecessary growth in database size. Access automatically compresses attachments, when it is possible, to minimize space usage. Do you want to attach a Word document to a record or save a series of digital pictures in your database? Using attachments make these tasks much easier. You can even add multiple attachments to a single record.

Note: You can use attachment fields in web databases, but each web table can have at most one attachment field.

Memo fields now store rich text and support revision history

This feature was introduced in Access 2007.

Rich text memo fields mean you are no longer limited to plain text in your records. You can format text with options (such as bold, italic, in addition to different fonts and colors, and other common formatting options) and store the text in your database. Rich-formatted text is stored in a Memo field in an HTML-based format that is compatible with the rich text data type in Windows SharePoint Services. You set the new TextFormat property to either RichText or PlainText, and the information is then correctly formatted in text box controls and in Datasheet view.

Memo fields are useful for storing large amounts of information. You can configure the Append Only property to retain a history of all changes to a Memo field. You can then view a history of those changes. This feature also supports the tracking feature in Windows SharePoint Services so that you can also use Access to view the SharePoint list content history.

Calendar for picking dates

This feature was introduced in Access 2007.

Fields and controls that use the Date/Time data type automatically gain support for a built-in interactive calendar, introduced in Access 2007. The calendar button automatically appears to the right of the date. Want to know the date for this coming Friday? Click the button and the calendar automatically appears to let you find and choose the date. You can opt to turn off the calendar for a field or a control by using a property.

Improved data presentation

New data presentation features help you create database objects more quickly and then analyze your data more easily.

Enhanced sorting and filtering tools

This feature was introduced in Access 2007.

Suppose that you have to quickly find a matching value or sort a column of data. The AutoFilter feature introduced in Office Access 2007 augments already powerful filtering abilities so that you can quickly focus on the data that you need. You can easily select from the unique values in a column, which is useful for situations when you can't recall the name that you need, or you can sort the values by using context menu options in plain language, such as Sort Oldest to Newest or Sort Smallest to Largest.

The most common filter options are easily visible in menu commands, or you can use quick filters to limit information based on the data that you enter. Quick filter options automatically change based on the data type so that you will see sensible choices for text, date, and numeric information.

Totals and alternating background colors in datasheets

This feature was introduced in Access 2007.

Datasheet view offers a Total row, in which you can show a sum, count, average, maximum, minimum, standard deviation, or variance. After you add the Total row, you point to the arrow in the column's cell and select the calculation that you want.

Datasheets, reports, and continuous forms support an alternating background color for rows, which you can configure independently of the default background color. Shading every other row is easy, and you can choose any color.

Conditional formatting

Access 2010 adds new conditional formatting features, enabling you to achieve some of the same formatting styles as are available in Excel. For example, you can now add Data Bars to make a column of numbers more readable, as shown in the following illustration:

Note: Conditional formatting is not available in web databases.

Enhanced security

Enhanced security features and strong integration with Windows SharePoint Services help you to more effectively manage and allow you to make your information tracking applications more secure than before. By storing your tracking application data in lists on Windows SharePoint Services, you can audit revision history, recover deleted information, and configure data access permissions.

Office Access 2007 introduced a new security model that has been carried forward and improved in Access 2010. Unified trust decisions are integrated with the Microsoft Office Trust Center. Trusted locations make it easy to trust all databases in secure folders. You can load an Office Access 2007 application that has code or macros that are disabled to provide a more secure, sandboxed (that is, unsafe commands can't be run) experience. Trusted macros are run in Sandbox mode.

Collect data by using InfoPath forms and Outlook

This feature was introduced in Access 2007.

The Data Collection feature helps you collect feedback by using Outlook and, optionally, InfoPath. You can automatically generate an InfoPath form or an HTML form and embed it in the body of an e-mail message. You can then send the form to recipients selected from your Outlook contacts, or to the names of recipients stored in a field in your Access database.

You choose whether to collect new information or update existing information. Recipients then complete the form and return it. Outlook recognizes the incoming forms and automatically saves the data in your Access database — no retyping required.

Export to PDF and XPS

In Access 2010, you can export data to a PDF (Portable Document Format) or XPS (XML Paper Specification) file format for printing, posting, and e-mail distribution, provided that you first install the Publish as PDF or XPS add-in. Exporting a form, report, or datasheet to a .pdf or .xps file lets you capture information in an easy-to-distribute form that retains all your formatting characteristics, but doesn't require others to install Access on their computers to print or review your output.

• Portable Document Format    A PDF is a fixed-layout electronic file format that preserves document formatting and helps with file sharing. The PDF format makes sure that when the file is viewed online or printed, it retains exactly the format that you intended, and the data in the file cannot be easily copied or changed. The PDF format is also useful for documents that will be reproduced by using commercial printing methods.

• XML Paper Specification    An XPS is an electronic file format that preserves document formatting and helps with file sharing. The XPS format makes sure that when the file is viewed online or printed, it retains exactly the format that you intended, and the data in the file cannot be easily copied or changed.

Working with external data made easier

You can now connect to a Web service as an external data source. You'll need a Web service definition file provided by the Web service administrator. After you install the definition file, you can link to Web service data as a linked table.

New features introduced in Office Access 2007 make it easier to import and export data. You can save an import or export operation and then reuse the saved operation the next time that you want to perform the same task. The Import Spreadsheet Wizard lets you override the data type chosen by Access, and you can import, export, and link to files that have the Office Excel 2007 at least file formats.

A better way to troubleshoot problems

This feature was introduced in Access 2007.

Microsoft Office Diagnostics is a series of diagnostic tests that can help you discover why your computer is crashing. The diagnostic tests can solve some problems directly and can identify ways that you can solve other problems. Microsoft Office Diagnostics replaces the following Microsoft Office 2003 features: Detect and Repair, and Microsoft Office Application Recovery.

Improved spelling checker

This feature was introduced in Access 2007.

The following are some new features of the spelling checker.

• The spelling checker behaves more consistently across the Office programs. Examples of this change include the following:

  • Several spelling checker options are now global. If you change one of these options in one Office program, that option is also changed for all the other Office programs.

  • In addition to sharing the same custom dictionaries, all programs can manage them by using the same dialog box. For more information, see the article Use custom dictionaries to add words to the spelling checker.

• The spelling checker includes the post-reform French dictionary. In Microsoft Office 2003, this was an add-in that had to be separately installed.

• An exclusion dictionary is automatically created for a language the first time that language is used. You can use exclusion dictionaries to force the spelling checker to flag words you want to avoid using. Exclusion dictionaries are useful for avoiding words that are obscene or that don't match your style guide. For more information, see the article Use exclusion dictionaries to specify a preferred spelling for a word.