Use an Access table as a secondary data source in a browser-compatible InfoPath form

Use Microsoft Access' feature to link a table to a SharePoint list and then use this list as a secondary data source in a browser-compatible InfoPath form.

ADVERTISEMENTS

Problem

You have a Microsoft Access 2007 database that contains tables you would like to use as secondary data sources in an InfoPath browser form, but InfoPath 2007 does not allow data connections to Access databases when a form is browser-compatible.

Solution

Use Microsoft Access' feature to link a table to a SharePoint list and then use this list as a secondary data source in a browser-compatible InfoPath form.

Discussion

This solution would be suitable for users in your organization who are familiar with Microsoft Access and who would like to keep Microsoft Access as their main application for maintaining data which is used in lists in your organization's InfoPath browser forms. It offers easy dynamic maintenance of lists in InfoPath browser forms.

You can accomplish this functionality as follows:

  1. Open Microsoft Access 2007 and create a new database.
  2. Create a table that has an ID and a Name field.


    Figure 1. Fruits table in Microsoft Access 2007.
  3. Save the table and give it a name. Here we will call our table Fruits.
  4. On the External Data tab in the SharePoint Lists group, click Move to SharePoint, and follow the instructions in the Move to SharePoint Site Wizard to link the Access table to a SharePoint list.


    Figure 2. Move to SharePoint button in the SharePoint Lists group on the External Data tab in Microsoft Access 2007.

    At this point, whenever data is added, edited, or deleted, in either the SharePoint list or the Microsoft Access table, these two sources will be updated and synchronized with each other to contain the same data.


    Figure 3. SharePoint list that is linked to the Access table.
  5. Open Microsoft Office InfoPath 2007.
  6. On the Getting Started dialog box, click Design a Form Template.
  7. On the Design a Form Template dialog box, check the Enable browser-compatible features only, and click OK.
  8. Add a Drop-Down List Box to the form template and modify the form template so that it resembles the following figure.


    Figure 4. InfoPath form template in Design mode.
  9. Double-click on the Drop-Down List Box to open its Properties dialog box.
  10. Select Look up values from an external data source in the List box entries section on the Data tab.
  11. Click Add and follow the instructions on the Data Connection Wizard to add a Receive data data connection to the SharePoint list you created in step 4.
  12. Set the Entries, Value, and Display name for the Drop-Down List Box as shown in the following figure, and then click OK to close the Properties dialog box.


    Figure 5. Data source settings on the Properties dialog box of the drop-down list box.
  13. Save the form template.
  14. On the File menu, click Publish.
  15. On the Publishing Wizard dialog box, select To a SharePoint server with or without InfoPath Forms Services, and click Next.
  16. On the Publishing Wizard dialog box, enter the location of your SharePoint or InfoPath Forms Services site, and click Next.
  17. On the Publishing Wizard dialog box, make sure Enable this form to be filled out by using a browser is checked and that Document Library is selected, and click Next.
  18. On the Publishing Wizard dialog box, select Create a new document library, and click Next.
  19. On the Publishing Wizard dialog box, type a name and description for the document library, and click Next.
  20. Click Next again, then Publish, and then Close.
  21. Open a web browser and navigate to the document library you just created.
  22. Click Settings and then select Form Library Settings from the menu that appears.
  23. On the Customize page for the library, click Advanced settings under General Settings.
  24. On the Advanced Settings page for the library, select Display as Web page under Browser-enabled Documents, and click OK.
  25. Navigate back to the library and test your work by creating a New Document.


    Figure 6. InfoPath browser form with drop-down list box bound to a SharePoint list that is linked to an Access table.
 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services