Vision

Access Services: Creating web applications and publishing them to SharePoint 2010

Written by: Margriet Bruggeman, Nikander Bruggeman.

April 29, 2010

Access Services: Creating web applications and publishing them to SharePoint 2010

Every now and then a technology appears that promises that end users will be able to create CRUD (Create Read Update & Delete) applications in a point-and-click fashion. In the previous two versions of SharePoint, this role was acted out by SharePoint Designer. In the 2010 release, this role is fulfilled by Access 2010 in combination with the newly introduced Access services.

You may have worked with InfoPath and Forms Services before. InfoPath allows you to create a form, and as long as it's web compatible you can publish it, via Forms Services, to SharePoint. Once an end user opens such a form, Forms Services knows how to render it to HTML and JavaScript. Access Services is much like that. Access 2010 allows you to create a web application and publish it, via Access Services, to SharePoint 2010. Since we feel the greatest strength of Access is, and has been for years, the ease with which you can create CRUD applications we think it is worth your time taking a look at how to integrate Access 2010 web applications with SharePoint 2010.

You can either use a browser or Access as the client to communicate with Access publications that have been published through Access services (from now on, we will call such applications: Access web applications). Both clients communicate with Access Services, although a browser leverages the data form web part (which renders Access forms and reports) and the Project datasheet object (which renders Access datasheets) to do so, whereas the Access client directly talks to Access Services. In the middle tier, an Access services web service provides query processing and data access logic, which mainly have to do with caching of data and performance optimization. Ultimately, this middle tier is responsible for managing all application data, which is stored in SharePoint lists. Any advanced forms of business logic you may have implemented in Access via macros will be implemented by Access Services with the help of list event handlers, SharePoint workflows and QuickFlows.

In the rest of this blog post, we will create a small Access web application that contains a Products table and a Purchases table. We will create the required database tables for this application, create an overview/detail forms for it, and publish it to SharePoint 2010 using Access Services. In the following procedure, we will create a new database and a new table that will hold Product information.

  1. Click Start > All Programs > Microsoft Office > Microsoft Access 2010.
  2. On the File tab, ensure that the New section is highlighted and choose Blank Web Database.
  3. In the File Name textbox (located in the Blank database pane), specify the following value: ProductDatabase.accdb.
  4. Click the Create button. This causes the creation of a new database and allows you to create a new table.
  5. In the Table Designer window, click the Click to Add column header and choose Text.
  6. Rename Field1 to Product.
  7. Add another Text column and call it: Description.
  8. Add a new Date & Time column and call it: Available.
  9. Click the Save button and name the table Products.

You have created a simple products table. Provide some test data for it and save the data. Our Products table looks like this:

Next, add a new table that will contain purchases of these products.

  1. Click the Create tab and choose Table. This opens a new table.
  2. Add a new column of type Number and rename it to: Item_Total.
  3. Add a new column of type Currency and rename it to: Price.
  4. Add a new column of type Lookup & Relationship. This opens the Lookup wizard.
  5. Make sure the option I want the lookup field to get the values from another table or query is selected and click Next.
  6. Select the Products table. Click Next.
  7. Select the ID field and click the > button.
  8. Select the Product field and click the > button.
  9. Click Next.
  10. Specify the Product column in the Sort order drop down list. This orders items according to their product name.
  11. Click Next.
  12. Make sure the Hide key column checkbox is selected. Accept all other default values and click Next.
  13. Now, you are asked to provide a label for your lookup column. Just call it: Product.
  14. Click the Enable Data Integrity checkbox and accept the Restrict Delete option which prevents Products from being deleted accidentally.
  15. Click Finish. This opens the Save As dialog window.
  16. In the Table Name textbox, enter the value: Purchases. Click OK.

Next, we will add a macro that notifies a sales manager every time a new purchase takes place.

  1. Open the Purchases table by double-clicking it's name in the Tables section.
  2. Click the Table tab.
  3. Click the After Insert button. This opens the Macro Tools designer.
  4. Select the SendEmail action and specify a To address, a Subject, and a Body text.
  5. Click the Save button, followed by the Close button (located at the upper right hand corner).
  6. The next step is to create an Overview page of all current purchases.
  7. Double-click the Purchases table in the Tables section of the Navigation pane. This opens the Purchases table.
  8. Click the Create tab.
  9. Click Datasheet. This opens a Datasheet window.
  10. Save the Overview form by pressing CTRL+S. A Save As dialog window appears.
  11. Enter the following Form Name: PurchaseOverview.
  12. Click OK.

You should notice that the Navigation pane now holds a new section called Forms, which in its turn contains the PurchaseOverview form you have just created. Now we will also create a purchase details form.

  1. Again, click the Purchases table.
  2. Click the Create tab, then click the Form button. This creates a new form called Purchases, containing all fields in the table.
  3. Click the Save button and name the form: PurchaseDetail. Click OK.

Now you should have two forms. In the next part we will create a navigation form.

  1. Again, click the Purchases table.
  2. Click the Create tab, then click Navigation > Horizontal tabs. This opens a new Navigation form.
  3. Drag-n-drop the PurchaseOverview form on the Navigation form.
  4. Double-click the PurchaseOverview tab and rename it to Overview of purchases. The Navigation form should now look similar to this:
  5. Save the form and call it: Home, then click OK.

In the next step, we will enhance the Home Navigation form so that you can open a detail form from the overview form.

  1. Right-click the Home Navigation form and choose Layout View. This opens the Layout View of the form.
  2. Select the ID column.
  3. Press F4 to open the Property Sheet of this column.
  4. Click the Event tab.
  5. Click the On Dbl Click event, then click the … (Property Builder) button. This opens the Choose Builder dialog window.
  6. Select the Macro Builder and click OK.
  7. Select the If action.
  8. In the If condition text box, add the following value: Not IsNull([ID]).
  9. In the Add New Action drop down list, choose OpenForm.
  10. In the Form Name drop down list, select the PurchaseDetail form.
  11. In the Where Condition text box, enter the value: [ID] = [Forms]![Home]![NavigationSubForm].[Form]![ID]. Don't worry, IntelliSense will help you accomplish this.
  12. In the Data Mode drop down list, select Edit.
  13. Set the Window Mode drop down list to Dialog.

At this point, the Macro should look like this:

Go ahead and save and close the Macro. You can reopen it any time via the Event tab of the Property sheet. Next, you will need to specify a default form that opens once users start interacting with the Products database.

  1. Click the File tab, then click Options. This opens the Access Options dialog window.
  2. Select the Current Database option.
  3. In the Display Form drop down list, select Home.
  4. Click OK. An informational message appears stating that you must close and reopen the current database for the specified option to take effect.
  5. Click OK.

Close Access 2010 and re-open it, then re-open the ProductDatabase database. This time, the Home Navigation Form should be selected by default. Now it's time to publish the application to SharePoint 2010. First of all, you should have a SharePoint site that you want to publish the Access application to. In our example, we will use a site called AccessHost located on a SharePoint site collection at http://sharepoint2010.

  1. Click the File tab, then click the Share option.
  2. Locate the Share section and click Publish to Access Services.
  3. Click the Run Compatibility Checker button. This may ask you to close any objects you still have open. The main thing this button does is check to see if your application is web compatible (in other words: is supported in Access services).
  4. Locate the Publish to Access Services section.
  5. In the Server URL text box, add the value: http://sharepoint2010.
  6. In the Site Name text box, add the value: MyAccessApp.
  7. Click the Publish to Access Services button.

The publication process shouldn't take long and result in the following success message:

Click on the URL that is displayed in the Publish Access Application dialog window to open your custom Access application, that is now published to Access services and can now be accessed as a normal web application. First, it will open the default navigation form you have created:

You can double click on the ID field to open the Detail form: Access Services has taken the Macro we've created earlier and translated it to JavaScript. The Detail form itself looks like this:

If at one time you feel the need to change your web application, you can do this by opening your database, click on the Info tab, and then click the Sync All button.

In the final part of this section, we will discuss how to deploy Access web applications. In essence, the steps for creating an Access web application remain the same, only you won't publish the Access application directly to Access services. Instead, all you have to do is create a template of your Access application, upload it as a user solution. Once you have done that, you can create new SharePoint sites that are based on this template to create new instances of your Access web application. The next procedure explains how to deploy an Access web application via a template.

  1. Open your Access application.
  2. Click the File tab.
  3. Click Share.
  4. In the File Types section, click Save Database As.
  5. In the Save Database As section, under Database File Types, click Template. This is shown in Figure x. This opens the Create New Template from This Database dialog window.
  6. In our case, we will create a new template and call it Purchases Web App Template, and choose to include the current data in the template.
  7. Click OK. You should see an informational message telling you that the template file (*.accdt file) is save locally.
  8. Now, open the SharePoint 2010 site collection where you want to add the Access web application template.
  9. In the root site of the SharePoint 2010 site collection, click Site Actions > Site Settings. This opens the Site Settings page of the root site of the SharePoint 2010 site collection.
  10. In the Galleries section, click Solutions. This opens the site collection Solutions page.
  11. Click the Solutions tab.
  12. Click the Upload Solution button. This opens the Upload Document dialog window.
  13. Browse to your template file, double-click it, then click OK. This adds the solution (a normal SharePoint *.wsp file) to your gallery.
  14. Then, select the template file, choose the item action menu and click Activate. This opens the Solution Gallery - Activate Solution dialog window.
  15. Click the Activate button.
  16. Go back to the site collection and choose Site Actions > New Site. This opens the Create page.
  17. Among the options listed on the Create page, you should see your own custom template. If you select it and create a new site, you will have successfully created a new instance of your Access web application in the form of a new SharePoint site.

As you can see, the concept of Access templates that can be exported as user solutions make it extremely easy to deploy and reuse Access web applications.

« back to overview page