Microsoft Office Access 2007 provides a powerful set of tools that help you to quickly start tracking, reporting, and sharing information. You can rapidly create attractive and functional tracking applications by customizing one of several predefined templates, converting an existing database, or creating a new database, and you can do so without deep database knowledge. By using Office Access 2007, it is easy to adapt database applications and reports to changing business needs. The enhanced support for Windows SharePoint Services 3.0 in Office Access 2007 helps you to share, manage, audit, and back up your data.
In this article
Faster ways to get started
Office Access 2007 provides templates with which you can easily create databases, tables, and fields. In addition, there are new views and design tools to help you create new database objects and to work with data.
Database templates for building complete applications
Office Access 2007 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. Use the Getting Started with Microsoft Office Access page, which appears each time that you start Office Access 2007, and quickly get started creating your database by opening one of the templates.
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 Office Access 2007, you can connect to Microsoft Office Online and download more templates.
Field and table templates
Spending a lot of time designing your fields? Instead, use the new field templates, which are predefined fields, each with a name, data type, length, and preset properties. You can drag the fields that you need directly from the Field Templates pane to a datasheet. Field templates are based on XML Schema Definition (.xsd) files so that you can set up your own standard definitions for shared use in your department or workgroup.
In addition, Office Access 2007 includes table templates for tables that are commonly used in databases. For example, you can use the Contacts table template to add a Contacts table to your database. The table already includes commonly used fields, such as Last Name, First Name, and Address. Field properties are already set so that you can begin using the table immediately. There are other table templates available — Tasks, Issues, Events, and Assets — that include specialized fields designed for the type of data you want to store and track.
Improved Datasheet view
Creating tables is now easier — just click Table on the Create tab and start entering data. Office Access 2007 automatically determines the best data type for each field so that you are up and running in no time. The Add New Field 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, it is easy to do so by using the commands on the Ribbon, which is part of the Microsoft Office Fluent user interface. You can also paste data from Microsoft Office Excel tables into a new datasheet — Office Access 2007 creates all of the fields and recognizes the data types automatically.
1. The Add New Field column
Suppose you need to add an existing field from another table to your table. Just drag the field from the Field List pane onto the datasheet. Access automatically creates any needed relationships or prompts you throughout the process.
New 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.
For example, you can add a field to the design grid by dragging a field from the Field List pane, or you can change properties by using the property sheet. Layout view supports the new, stacked and tabular layouts — groups of controls that you can move and resize as a unit 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. Design view still remains available for more detailed work, and it has also been enhanced to support stacked and tabular layouts.
Results-oriented user interface
The new results-oriented user interface — the Microsoft Office Fluent user interface — makes it easy for you to work in Office Access 2007. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option. No matter what activity that you are performing in the new user interface — whether it is creating a report or entering data — Access presents the tools that are most useful to successfully completing that task.
Microsoft Office Fluent user interface The new Office Fluent user interface includes a standard area called the Ribbon, which contains 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.
Use the Office Fluent Ribbon to locate groups of related commands faster. For example, if you need to create a form or report, use one of the commands on the Create tab. The new design makes it easier to find the commands that you need, and you will discover features that you otherwise might not notice. Commands are placed closer to the surface, which means that you do not need to dig for them in menus or memorize their locations.
Key features of the Office Fluent user interface 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. Galleries are employed throughout the 2007 Microsoft Office system interface.
Quick Access Toolbar — A single standard toolbar that appears on the Ribbon, offering instant, single-click access to the most needed commands, such as Save and Undo.
Getting Started with Microsoft Office Access page This page provides quick access to a library of professionally designed database templates, in addition to databases that you recently opened and (if you are connected to the Internet) links to popular Office Online articles.
Navigation Pane This 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 Your tables, queries, forms, reports, and macros are displayed as tabbed objects in the Access window.
By clicking the object tabs, you can easily switch between various objects.
Status bar The bar at the bottom of the window that displays status information and includes buttons that let you switch between views.
Mini toolbar An element, similar to a toolbar, that transparently appears above text that you selected so that you can easily apply formatting, such as bold or italic, or change the font.
Help window Unlike previous versions of Access, Office Access 2007 offers easy access to both Access Help and Developer Reference content from the same Help window. You can easily change the scope of your search to the Developer Reference content only, for example. Regardless of the settings that you make in the Help window, all of the Access Help and Developer Reference content is always available on Office Online.
More powerful object creation tools
Office Access 2007 provides an intuitive environment for creating forms and reports, allowing you to quickly create forms and reports that display sorted, filtered, and grouped information.
Use the Create tab to quickly create new forms, reports, tables, Windows SharePoint Services lists, queries, macros, and modules. If you selected 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 are more visually appealing and immediately useful, because their design has been upgraded. 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 date and time information, and informative footers and totals.
You will find that objects created by using the new, quick create experience take you farther with less effort.
New views: Report view and Layout view
Two new views 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.
Report view adds the ability to browse, but Layout view lets you make design changes while you browse. 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 supports the new, stacked and tabular layouts — groups of controls that you can manipulate 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. Design view remains available for more detailed work, and it has also been enhanced to support layouts.
Streamlined creation of grouping and sorting in reports
New to Office Access 2007 is a better and more accessible way to group and sort in reports and add totals. You will find the new user interface easier 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 need to see total sales by region in a report. Use Layout view and the new Group, Sort, and Total pane to add a group level and request a total — and see the changes live, in your report! The new 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.
New control layouts to help 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 of the fields for a customer. You can use Office Access 2007 to 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.
Layouts are saved with your design, so they remain available.
New split forms for fast browsing of data
Use the new split form to create a form that combines a Datasheet view and a Form view. You can set a property to tell Access to place the datasheet on the top, bottom, left, or right.
Embedded macros in forms and reports
Use the new, trusted, 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 modify 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 certain, potentially unsafe, operations.
New data types and controls
Office Access 2007 includes new and enhanced data types and controls that allow you to store more types of data, and you can enter them more easily.
Multivalued fields for complex data
You can create a field that holds multiple values, also called complex data. Suppose that you need 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 earlier versions of Access, you needed to create a many-to-many relationship in order to do this correctly. In Office Access 2007, the hard part is done for you, when you choose a field that you set to accept multiple values. Multivalued fields are especially appropriate when you use Office Access 2007 to work with a SharePoint list that contains one of the multivalued field types used in Windows SharePoint Services. Office Access 2007 is compatible with these data types.
Attachment fields for files
The new Attachment data type lets you easily store all types of documents and binary files in your database without any unnecessary growth in database size. Office Access 2007 automatically compresses attachments, when possible, to minimize space usage. Do you need to attach a Microsoft Office Word 2007 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.
Memo fields now store rich text and support revision history
With the new, rich text support in Office Access 2007, 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 properly formatted in text box controls and in Datasheet view.
Memo fields are useful for storing large amounts of information. With Office Access 2007, you can set 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 your SharePoint list content history.
Calendar for picking dates
Fields and controls that employ the Date/Time data type automatically gain a new feature: support for a built-in interactive calendar for choosing a date. 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 design and analysis tools
New tools in Office Access 2007 help you to create database objects more quickly and then analyze your data more easily.
Enhanced Field List pane
The new Field List pane goes beyond the field picker found in earlier versions of Access, by including 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. Office Access 2007 is prepared for creating the infrastructure required, so if a relationship between tables is needed, it is automatically created, or you are prompted throughout the process.
Enhanced sorting and filtering tools
Suppose that you need to quickly find a matching value or sort a column of data. The new Office Access 2007 AutoFilter feature 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 readily 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.
These new filtering and sorting features are designed to provide you with a consistent experience, whether you are using Office Access 2007 or Microsoft Office Excel 2007.
Totals and alternating background colors in datasheets
New to Datasheet view is a Total row, in which you can display 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 set independently of the default background color. Shading every other row is easy, and you can choose any color.
Enhanced Office Access 2007 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 set data access permissions.
In order to help make Office Access 2007 more secure and to maintain consistency with other 2007 Office release products, Office Access 2007 includes new and improved security features. 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 with 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.
New ways to share data and collaborate with others
By using the new collaboration features of Office Access 2007, you can now, more effectively, collect information from others and share it in a security-enhanced environment on the Web.
Integration with Windows SharePoint Services
Office Access 2007 integrates with Windows SharePoint Services in a variety of ways, helping you to take advantage of the many collaboration features in Windows SharePoint Services.
Data revision tracking You can view revision history at the record level with the help of Windows SharePoint Services. Suppose that you need to know who changed a row or when it was changed. You can find out by viewing the revision history.
Permission setting You can set access permissions to SharePoint lists with the help of Windows SharePoint Services. You can selectively allow or deny certain users access. You can assign limited reading permissions or full editing rights.
Retrieve from the Recycle Bin You can use the new Recycle Bin in Windows SharePoint Services to easily view deleted records and recover information that was erroneously deleted.
Opening Access forms and reports in Windows SharePoint Services Windows SharePoint Services users can open lists in rich Access views directly from a SharePoint site. Office Access 2007 forms, reports, and datasheets can appear listed alongside other views on a SharePoint site. When you choose an Access view, Office Access 2007 starts and opens the requested form, report, or datasheet. This makes it easy for you to run a rich Office Access 2007 report on a SharePoint site without needing to first start Office Access 2007. You can also choose to open a list in Access, and if a database doesn't exist, it is created automatically and prepopulated with a set of forms and reports based on your list.
Ability to move your database to a SharePoint site You can easily change your standalone database to a Windows SharePoint Services shared solution that stores data in linked SharePoint lists. The Move to SharePoint Site Wizard is well prepared and automatically migrates primary keys and relationships in ways that make sense.
The immediate benefits of a Windows SharePoint Services shared solution include shared browser-based access, centralized backup, database deployment based on a SharePoint site, and all of the benefits of using a rich, Access-based client in a server-based solution. IT departments gain a measure of predictability and control by sourcing database access on a SharePoint site. Users can subscribe to e-mail alerts for when records are added, deleted, or changed in a SharePoint list.
Integration with Windows SharePoint Services workflow Office Access 2007 supports workflow by using the Windows Workflow Services found in Windows SharePoint Services. You can create solutions that employ workflow routing and approval, that automatically assign tasks to other users, that report on project status, and that ensure that tasks are completed on time, for example.
Better performance for tables linked to SharePoint lists Optimizations that you make to the internal processing of tables linked to SharePoint lists provide a faster, smoother experience.
All Windows SharePoint Services data types now map to Access data types With new support for multivalued fields and attachments, Office Access 2007 now supports all of the data types found in Windows SharePoint Services, making the design and building of Windows SharePoint Services shared applications much simpler.
Ability to take SharePoint lists offline with Access If you need to take some work home with you, you can take your SharePoint lists offline with one click. Work on your data in Access and then synchronize your changes, or reconnect with the SharePoint site at a later time.
Tip: Not sure which version to upgrade to? Be sure to take a look at the new features in Access 2010, such as databases that run in your browser, calculated fields in tables, and data macros.
Data collection by using InfoPath forms and Outlook
By using the new Data Collection feature, Office Access 2007 can automatically create a Microsoft Office InfoPath 2007 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. Office Outlook 2007 recognizes the incoming forms and automatically saves the data in your Office Access 2007 database — no retyping required.
Export to PDF and XPS
Now 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 of 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 ensures 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 ensures 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.
You can export data to a .pdf or an .xps format file from Office Access 2007 only after you install the add-in. For more information, see Enable support for other file formats, such as PDF and XPS.
Working with external data made easier
New features 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 need 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 the new Office Excel 2007 file formats.
Better ways to troubleshoot problems
Microsoft Office Diagnostics is a series of diagnostic tests that can help you to discover why your computer is crashing. The diagnostic tests can solve some problems directly and may 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.
For more information, see Diagnose and repair crashing Office programs by using Office Diagnostics.
Improved proofing tools
The following are some new features of the spelling checker.
The spelling checker has been made more consistent across the 2007 Microsoft Office system programs. Examples of this change include:
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 of the other Office programs. For more information, see Choose how spelling and grammar checking work.
In addition to sharing the same custom dictionaries, all programs can manage them by using the same dialog box. For more information, see Use custom dictionaries to add words to the spelling checker.
The 2007 Office release spelling checker includes the post-reform French dictionary. In Microsoft Office 2003, this was an add-in that had to be separately installed. For more information, see Choose how spelling and grammar checking work.
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 handy for avoiding words that are obscene or that don't match your style guide. For more information, see Use exclusion dictionaries to specify a preferred spelling for a word.