SharePoint Access Services will Re-Shape Application Development in 2010

This article provides an overview of how Access Services works, and introduces you to web database design. For an introduction to desktop databse design, see the article Database design basics.

Overview

Access Services provides a platform for you to create databases that you can use on the Web. You design and publish a web database by using Access 2010 and SharePoint, and people use the web database in a Web browser.

How it works

When you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.

 Note   To build a web database, you need Designer permissions on the SharePoint site where you want to publish it. For more information about SharePoint permissions, see the See Also section.

Forms and reports run in the browser

Forms, reports, and UI macros run inside the browser. This lets Access refresh data on the screen without having to redo the whole page.

You can create a form to help people navigate your application. A new control, the Navigation control, makes it easy to add standard Web-style navigation buttons to a form for this purpose.

 Note   The Navigation Pane (the feature that you use in Access to browse the objects in a database) is not available in a Web browser.

Data is stored in SharePoint lists

All of your tables become SharePoint lists, and records become list items. This lets you use SharePoint permissions to control access to your web database.

Queries and data macros run on the server

All SQL processing happens on the server. This helps improve network performance by limiting traffic to result sets.

Share on the Internet

You can use SharePoint Online to publish your web database to the Internet. SharePoint Online is an Internet-facing SharePoint deployment, hosted by Microsoft. For more information, see the See Also section.

 Note   This service is not available during beta.

Create a web database

This section describes key new features and provides steps for the basic design tasks you need to complete to create a web database.

There are a few tasks that you should perform before you start to design your web database. Moreover, there are design differences between web databases and desktop databases that you should know about, espcially if you are an experienced Access developer.

• Determine the purpose of your database   Have a clear plan so you can make good decisions when working out design details.
• Find and organize the information required   If you line up your data before you start designing, you can avoid having to refit your design to accommodate unexpected data challenges.
• Identify the SharePoint site you will use to publish   You cannot publish at all without SharePoint. If you want to test your design in a browser while you design it (not a bad idea), you have to publish it first.
• Plan your security   You can take advantage of SharePoint security to control access to your web database. Plan security early so you can build it into your design.

Design differences between desktop and web databases

Some database features that you can use in a desktop database are not available with Access Services. However, there are new features that support many of the same scenarios as these desktop-only features. If you are used to using one of these features, you should familiarize yourself with the ways these new features work.

The following table lists the desktop-only features, and the new feature that helps support the same scenario.

Desktop-only features with no Access Services counterpart

• Union queries
• Crosstab queries
• Overlapping controls on forms
• Table relationships
• Conditional formatting
• Various macro actions and expressions

 Note   You can create many client objects in a Web database, but you cannot use them in a browser. however, they are part of the Web database and can be used in Access 2010 on the desktop. People can open the Web database in Access, and then use the client objects. This is an effective way to share a database, and also opens new opportunities for working together over the Web. SharePoint handles any concurrency issues.

Consider using a template

When you have determined what your application must do, consider whether a database template would work. Database templates are pre-built applications that you can use as-is or modify to suit your particular needs.

You can review the available templates from the New place in Backstage view. For more information about the templates that come with Access 2010, see the article Introduction to the Access 2010 templates.

Get started with a blank web database

• Click the File tab, and then click New.

The File tab opens Backstage view, a new part of the Access interface where you’ll find commands that apply to an entire database, such as Publish to SharePoint. When Backstage view is displayed, the File tab label changes to Back.

In Backstage view, the New tab has commands for creating a database.

 Under Available Templates, click Blank Web Database.

 Review the proposed file name in the File Name box, and the path for the database file, listed just below. You can change the file name by typing in the File Name box.

 To change the path, click the folder icon next to the File Name box to browse for a location to put your database file.

 Click Create. Your new web database opens and displays a new empty table.

Design a Web table

 Note   You use Datasheet view to design a Web table.

When you first create a blank web database, Access creates a new table and opens it in Datasheet view.

After you modify and use the new table, you’ll most likely want to create more tables.

Create a new Web table

With your web database open:

• On the Create tab, in the Tables group, click Table.

When you first create a table, it has one field: an AutoNumber(AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it’s added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) ID field. You can add new fields to store the items of information required by the table subject. For example, you might want to add a field that stores the date you begin tracking something.

Add a field from the field gallery

You can choose from a variety of preformatted fields and add them to your table by using the field gallery.

1. On the Modify Fields tab, click Fields & Columns.
2. Click Add Field, and then choose the field that you want.

Add a field by typing a field name

1. With the table open, double-click Add New Field, and then type a name for the field.
2. Repeat for each field that you want to create.

Change field properties

When you create a new field by typing a field name, Access applies default formatting and properties to the field, such as assigning the text (text data type: In an Access project, a variable-length data type that can hold a maximum of 2^31 – 1 (2,147,483,647) characters; default length is 16.) data type. Formatting and properties determine how a field behaves, such as what kind of data it can store. You can change these settings so that the field behaves the way that you want.

1. Select the field that has formatting and properties that you want to set.
2. On the ribbon, click the Modify Fields tab.
3. Use the commands in the Formatting and Properties groups to change the settings.

Add a calculated field

You can add a field that displays a value that is calculated from other data in the same table. Data from other tables cannot be used as the source for the calculated data. Some expressions are not supported by calculated fields.

1. Select a new, empty field.
2. On the ribbon, click the Modify Fields tab.
3. In the Fields & Categories group, click Calculated Field.

The Expression Builder opens.

1. Use the Expression Builder to create the calculation for the field. Remember that you can only use other fields from the same table as data sources for the calculation. For Help using the Expression Builder, see the article Use the Expression Builder.

Set up data validation rules

You can use an expression to validate input for most fields. You can also use an expression to validate input for a table, which can be handy if you want to validate input for a field that does not support validation, or if you want to validate field input based on the value of a different field.

You can also specify the message that is displayed when a validation rule prevents input, known as a validation message.

Set up a field validation rule and message

1. Select the field to which you want to add a validation rule.
2. On the ribbon, click the Modify Fields tab.
3. In the Validation group, click Field Validation, and then click Validation Rule.

The Expression Builder opens.

1. Use the Expression Builder to create your validation rule. For Help using the Expression Builder, see the article Use the Expression Builder.
2. In the Validation group, click Field Validation, and then click Validation Message.
3. Type the message that you want to display when input data is not valid, and then click OK.

Set up a table validation rule and message

1. Open the table to which you want to add a validation rule.
2. On the ribbon, click the Modify Fields tab.
3. In the Validation group, click Table Validation, and then click Validation Rule.

The Expression Builder opens.

1. Use the Expression Builder to create your validation rule. For Help using the Expression Builder, see the article Use the Expression Builder.
2. In the Validation group, click Table Validation, and then click Validation Message.
3. Type the message that you want to display when input data is not valid, and then click OK.

Create a relationship between two Web tables

To create a relationship in a web database, you use the lookup wizard to create a lookup field. The lookup field goes in the table that is on the many- side of the relationship, and points to the table that is on the one- side of the relationship.

Maintain data integrity by using data macros

You can implement cascade updates and deletes by using data macros.

Create a Web query

You can use a query as the data source for forms and reports. Queries run on the server, helping minimize network traffic.

For example, suppose you use a Web database to track charitable contributions. You want to see who donated money while an event was occuring. You could use a query to select the data and prepare it for use in forms and reports.

 Note   This procedure uses the charitable contributions template as an example. You can follow along if you create a new database by using the charitable contributions database template.

1. On the Create tab, in the Other group, click Web Query.
2. In the Show Table dialog box, double-click each table that you want to include, and then click Close.

In this example, double-click Constituents, Donations, Events, and EventAttendees.

1. Create any required joins by dragging fields from one object to another in the query design window.

In this example, drag the ID field from Constitutents to the DonorConstituentID field in Donations, and then drag the DonorConstituentID field from Donations to the ConstituentID field in EventAttendees.

1. Add the fields that you want to use. You can drag the fields to the grid, or you can double-click a field to add it.

In this example, add Event from the table Events, DonationDate from the table Donations, and Greeting, FirstName, and LastName from the table Constituents.

1. Add any criteria that you want to apply.

In this example, you want to limit DonationDate so that it falls between the StartDate and EndDate of the event. In the query design grid, in the Criteriarow under DonationDate, type >=[StartDate] And <=[EndDate].

Create a Web form

Forms are the main way to enter and edit data in your Web database, and are also useful for reviewing data. Forms run in the browser, helping optimize performance. When you open a form, your browser gets the required data from the SharePoint server. You can filter and sort the data in the form without needing to get data from the server again.

 Tip   For best performance, limit the records retrieved by your main forms and reports.

1. Select a table or query to use as a data source.

 Note   If you want to create an unbound form, skip ths step.

1. On the Create tab, in the Forms group, click one of the following buttons:
   • Form   Create a simple form that shows one record at a time, using the object you selected as a data source.

 Note   If you are creating an unbound form, this button is not available.

• Multiple items   Create a form that shows multiple records at a time, using the object you selected as a data source.

 Note   If you are creating an unbound form, this button is not available.

• Blank form   Create a form that has nothing on it.
• Datasheet   Create a form that looks and behaves like a datasheet, using the object you selected as a data source.

 Note   If you are creating an unbound form, this button is not available.

Create a Web report

Reports are the main way to review or print data from your Web database. Reports run in the browser, helping optimize performance. When you open a report, your browser gets the required data from the SharePoint server. You can filter and sort data in the report without needing to get data from the server again.

 Tip   For best performance, limit the records retrieved by your main forms and reports.

1. Select a table or query to use as a data source.
2. On the Create tab, in the Reports group, click one of the following buttons:
   • Report   Create a basic report using the object you selected as a data source.
   • Blank Report   Create a report that has nothing on it.

Create a Navigation form

People need a way to navigate your application. Remember — the Navigation Pane is not available in a Web browser. For people to use your database objects, you must provide them a means. You can create a Navigation form and specify that it be displayed whenever someone opens your application in a Web browser.

 Tip   You may want to wait until last to create your Navigation form, so you can add all your objects to the form when you create it.

1. On the ribbon, click the Create tab.
2. In the Forms group, click Navigation, and then select a navigation layout from the list.
3. To add an item, drag it from the Navigation Pane to the Navigation control.

 Note   You can only add forms and reports to a Navigation control.

1. Add any other controls that you want to the body of the Navigation form. For example, you might want to provide search functionality across all forms by adding a few controls to your Navigation form.

Publish and synchronize changes to your application

Before you publish a Web database, consider running the Compatibility Checker. The Compatibility Checker examines your database objects for issues that would prevent them from publishing correctly. If no issues emerge, the Compatibility Checker reports that your database is Web-compatible. If problems are discovered, the Compatibility Checker creates a table that lists the issues.

1. Click the File tab.
2. Under Information about database_name, click Run Compatibility Checker.

Publish a Web database

1. Click the File tab, and then click Share.
2. Under Special locations, click Publish to SharePoint.
3. In the middle of the tab, under Publish to SharePoint, fill in the following:
   • In the Web Locationbox, type the Web address of the SharePoint server where you want to publish your database. For example, http://Contoso/.
   • In the Web Application Name box, type a name for your Web database. This name will be appended to the Web Location to produce the URL for your application.

For example, if the Web Location is http://Contoso/ and the Web Application Name is CustomerService, the URL is http://Contoso/CustomerService.

1. Click Publish to SharePoint.

Synchronize a Web database

After you make design changes or take a database offline, you eventually want to synchronize. Synchronizing resolves differences between the database file on your computer and the Web application on the SharePoint server.

1. Open the Web database in Access.
2. Click the File tab, and then click Synchronize.