How to Create and Publish a Web Database in Microsoft Access 2010
Microsoft 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 tutorial 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.
Building an Access 2010 Web Database
1. Open Microsoft Access 2010.
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.
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.
4. Save the changes to the table and set its name to Suppliers when you are prompted.
5. Next, 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:
|Field Name||Field Type|
|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.
Publishing 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.
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.
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.
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.
Article Contributor: Satish Kumar