Export (0) Print
Expand All
55 out of 69 rated this helpful - Rate this topic

Creating Web Databases with Access 2010 and Access Services

Office 2010
Office Visual How To

Summary:  Learn how to create and publish a Microsoft Access 2010 database to Access Services on Microsoft SharePoint Server 2010. (8 printed pages)

Applies to:  Microsoft Access 2010, Microsoft SharePoint Server 2010, Microsoft Access Services

Provided by:   Joel Krist, iSoftStone

February 2010

Overview

Access 2010 offers many new features and improvements. The improved integration with SharePoint Server 2010 via Access Services allows for Access 2010 databases to be published to SharePoint, which enables multiple users to interact with the database application from any standards-compliant Web browser. This Visual How To shows how to create an Access 2010 Web database and publish it to Access Services on SharePoint Server 2010.

Code It

Download the sample code

This Visual How To shows how to create an Access 2010 Web database, publish the database to SharePoint Server 2010, and use the new Web interface to work with the database solution via the Web browser.

Creating an Access 2010 Web Database

This Visual How To shows Web database publishing and usage instead of showing how to create a complex solution that uses all the new features in Access 2010.

The following procedure describes how to create an Access 2010 Web database.

To create an Access 2010 Web database

  1. Start Access 2010. Access displays the Microsoft Office Backstage view. This allows a new database to be created.

  2. Select the New tab in the left navigation pane and then select Blank Web database from the Available Templates. Creating a Web database helps ensure that the features that are used by the database will be compatible with the Web publishing feature. For more information about Web compatibility, see the Read It section in this article.

    Figure 1. Selecting blank Web database template

    Selecting Blank Web Database Template
  3. Change the File Name to PartsAndSuppliers and then click Create. After Access creates a local copy of the database that contains a single table named Table1, add the following fields to the Table1 table.

    Table 1. Table1 table fields

    Field Name

    Field Type

    Supplier Name

    Text

  4. Save the changes to the table and set its name to Suppliers when you are prompted.

  5. Add a Web form to the database that will allow for data to be entered into the Suppliers table one row at a time. Select the Create tab on the ribbon and then click Form in the Forms ribbon group. Access will create the new Web form for the Suppliers table.

  6. Save the changes to the database. Access will prompt you for the new form name. Accept the default name "Suppliers".

  7. Create a new table. Select the Create tab on the ribbon and then click the Table button in the Tables ribbon group. Add the following fields to the new table.

    Table 2. New table fields

    Field Name

    Field Type

    Part Name

    Text

    Supplier

    Lookup & Relationship

  8. When the Lookup & Relationship field type is specified, Access starts the Lookup Wizard to configure the field. In the first page of the Lookup Wizard, specify that the new field gets its values from another table and then click Next.

  9. On the second page of the Lookup Wizard, select the Suppliers table as the source table for lookup values and then click Next.

  10. On the third page of the Lookup Wizard, select the Supplier Name field as the source field for lookup values and then click Next.

  11. On the fourth page of the Lookup Wizard, sort the lookup values displayed in the list in ascending order by Supplier Name and then click Next.

  12. On the fifth page of the Lookup Wizard, click Name the New Field Supplier and then click Finish.

    Access will prompt you for the new table name. Name the new table Parts and then click OK.

  13. Add a Web form to the database that will allow for data to be entered into the Parts table one row at a time. Select the Create tab on the ribbon and then click Form in the Forms ribbon group. Access creates the new Web form for the Parts table. Save the changes to the database and accept the default name of “Parts” for the new Web form.

  14. Add a form to the database that uses the Navigation control to enable navigation between forms. Select the Create tab on the ribbon and then click Navigation in the Forms ribbon group. Select the Horizontal Tabs item from the drop-down menu.

    Access creates a new navigation form with the Navigation control. Save the changes to the database. When prompted, accept the default name for the navigation form.

  15. Add tabs to the Navigation control on the navigation form. Drag the Parts and Suppliers Web forms to the Navigation control on the navigation form. Save the changes to the database.

  16. Configure the Navigation Form as the default Web form so that it will be displayed the first time that a user opens the Web database on Access Services.

  17. Select the File tab on the ribbon and then Options in the left navigation pane. Access displays the Access Options form. Select the Current Database category and change the Web Display Form setting to the Navigation Form. Click OK to save the changes.

The following procedure describes how to publish a database to SharePoint Server 2010.

To publish a database to SharePoint Server 2010

  1. Select the File tab in the ribbon to display the Backstage view. The Info tab in the left navigation pane will be selected and information about the Web database will be displayed.

    Figure 2. The Backstage View

    The Backstage View
  2. Click Publish to Access Services. The Access Services Overview pane will be displayed, offering options to check the database for Web compatibility and publish it to Access Services. The compatibility checker allows the database to be tested to verify that it has no items or settings that would make it incompatible with Access Services. In this case, because the sample database is based on the Web Database template and the solution is designed to be simple, skip this step.

  3. In the Publish to Access Services section, specify the URL of the SharePoint site to publish the database to. Specify PartsAndSuppliers for the site name and then click Publish to Access Services.

    Figure 3. Publishing to Access Services

    Publishing to Access Services
  4. SharePoint prompts for connection credentials. Enter the name and password of a user who has permission to create new sites on the SharePoint Server and then click OK. Access processes the objects in the database and publishes it to SharePoint, synchronizing the local copy of the database that has the data stored on the server. When the database is published, Access displays a Publish Succeeded message that has a link to the new Web database application.

  5. Click the link to the new site to open the Web application in the Web browser. Access Services displays the navigation form.

    Figure 4. Web database in the Web browser

    Web Database in the Web Browser

The following procedure describes how to use the new Web interface to work with the database via the Web browser.

To use the new Web interface to work with the database via the Web browser

  1. Add a supplier to the Suppliers table. Select the Suppliers tab on the navigation form to display the Suppliers Web form. Specify a supplier name in the form and then click the floppy disk icon to save the new record to the Suppliers table.

  2. Add a new part to the Parts table. Select the Parts tab on the navigation form to display the Parts Web form. Specify a part name in the form, select the supplier previously added, and then click the floppy disk icon to save the new record to the Parts table.

  3. In the Access client, open the Parts table. Be aware that the local copy of the database was automatically updated and the new record entered via the Web browser is shown in the Parts table.

  4. Add a new record to the Parts table in the local copy of the database that has the Access client.

  5. In the Web application, refresh the browser. Be aware that the Parts Web form shows that the Parts table now contains the second record previously added by using the Access client.

Read It

Manage and Share Data

Access 2010 and Access Services are designed to make it easier to manage, analyze, and share data. By using Access 2010 and Access Services, users can publish their Access database solutions to SharePoint Server 2010, which enables interaction with the solution from any device that can run a standards-compliant Web browser.

Access Web databases published to SharePoint Server can use standard objects such as tables, queries, forms, macros, and reports. Access Services stores those objects in SharePoint and displays the Web application that has a fidelity that rivals that provided by the Access client.

Data Caching and Offline Support

Data caching between the Access client and Access Services enables end users and developers to make offline changes to a Web database and its data and have those changes be automatically synchronized with the server upon reconnection. Access 2010 provides improvements to the link table cache. List data is cached in local tables to improve large list performance.

Web Compatibility Checking

The Access client provides Web compatibility checking and a design-for-Web mode that allows for database solution creators to be sure that their applications are compatible with publishing to the Web and allows them to save valuable time by avoiding the inclusion of incompatible objects.

The following examples are issues that will make a database incompatible with publishing to SharePoint, grouped by category.

Forms and Reports

Not all form control events are supported in Web databases. Supported control events include the following:

  • AfterUpdate

  • OnApplyFilt

  • OnChange

  • OnClick

  • OnCurrent

  • OnDblClick

  • OnDirty

  • OnLoad

Bound controls are not supported in the report header, report page header, or report page footer. The control must be unbound or moved to the detail section of the report or to the report footer.

Relationships and Lookups

Tables with lookup columns must have a primary key and the primary key must be a long data type. Both the source and target fields of the lookup must be long integers.

Not all column data types are compatible with Web lookups. The lookup field must be one of the following supported data types:

  • Single line of text

  • Date/Time

  • Number

  • Calculated field that returns a single line of text

Schemas

Not all field data types are compatible with the Web. Supported field data types are as follows:

  • Text

  • Number

  • Currency

  • Yes/No

  • Date/Time

  • Calculated Field

  • Attachment

  • Hyperlink

  • Memo

  • Lookup

Tables with more than 220 fields are incompatible with the Web.

General Issues

Certain characters that are used in property values are incompatible with the Web. In order for an object or control name to be compatible it must not violate any of the following rules:

  • The name must not contain a period (.), an exclamation point (!), a set of square brackets ([ ]), a leading space, or a non-printable character such as a carriage return.

  • The name must not contain the following characters: / \ : * ? "" < > | # <TAB> { } % ~ &.

  • The name must not begin with an equal sign (=).

  • The name must be from 1 through 64 characters long.

See It

Watch the video

Video Splash Screen

Length: 07:44

Click to grab code

Grab the Code

Explore It
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.