Add a data connection to a Microsoft Access database

If your users will need data from a Microsoft Office Access 2007 (.accdb format) database or from an Access database saved in an earlier version (.mdb format) to fill out a form based on your Microsoft Office InfoPath form template, you can add a secondary data connection to your form template that queries an Access database.

You can add a secondary data connection, but that data connection can only query an Access database. You cannot add a secondary data connection that submits form data to an Access database.

If you want your users to submit their form data to an Access database, you can design a form template that is based on an Access database, and then enable the submit data connection in the main data connection. Alternatively, you can add a secondary data connection to a Web service that works with your Access database.

Find links to more information about designing form templates that are based on an Access database and adding a secondary data connection to a Web service in the See Also section.

In this article


Overview

A secondary data connection (data connection: The connection between an InfoPath form and an external data source, such as a database, Web service, SharePoint library, or XML file. Data connections are used to query and submit data.) is a data connection that you add to a form template. A secondary data connection is different from the main data connection, which you create when you design a new form template that is based on a database or a Web service. A form template's main data connection can receive data from an external data source and can also submit form data to an external data source. However, a form template can have only one main data connection.

You can add as many secondary data connections to a form template as you want. For example, suppose that you have an Access database that contains a table that stores employee data and another table that stores customer data. You can add a secondary data connection that retrieves data from the employee table, and you can also add another secondary data connection that retrieves data from the customer table in the same Access database.

Although you can add a secondary data connection to your form template that can submit form data to an external data source, such as a Web service or Windows SharePoint Services library, you can only use secondary data connections to retrieve data from an Access database. You cannot add a secondary data connection that submits form data to an Access database.

When you add a query data connection to a database, InfoPath creates a secondary data source (data source: The collection of fields and groups that define and store the data for an InfoPath form. Controls in the form are bound to the fields and groups in the data source.) that contains data fields (data field: A field that contains the result of a query to an external data source.) and groups (group: An element in the data source that can contain fields and other groups. Controls that contain other controls, such as repeating tables and sections, are bound to groups.) that correspond to the way that the data is stored in the database. Because the data structure in the secondary data source must match the way that data is stored in the database tables, you cannot modify the fields or groups in the secondary data source.

You can configure each query data connection to save its results so that users have access to the data when their form is not connected to a network. Depending on the nature of the data, you might want to display the query results only when users are connected to a network.

Security  If you are using a secondary data connection to retrieve sensitive data from an external data source, you may want to disable this feature to help protect the data from unauthorized use in case the computer is lost or stolen. If you disable this feature, the data will be available only if the user is connected to the network.

When you add a query data connection to a form template, by default the forms that are based on this form template use the data connection when they are opened by a user. You can also configure your form template to use the query data connection in one of the following ways:

  • Add a rule    You can configure a rule to use the query data connection whenever the condition in the rule occurs.
  • Add a button    You can add a button to the form template that users can click to get data by using the query data connection.
  • Use custom code    If you cannot add a rule or button, you can use custom code to get data by using the query data connection. Using custom code requires a developer to create the custom code.

Top of Page Top of Page

Compatibility considerations

You cannot add a data connection to an Access database to a browser-compatible form template (browser-compatible form template: A form template that is designed in InfoPath by using a specific compatibility mode. A browser-compatible form template can be browser-enabled when it is published to a server running InfoPath Forms Services.).

Top of Page Top of Page

Before you begin

Before you add a secondary data connection to an Access database to your form template, you need the following information:

  • The name and location of the database.

 Note   If your Access database is stored in a network location, make sure that your database is accessible to your users.

Top of Page Top of Page

Step 1: Add a secondary data connection

  1. On the Tools menu, click Data Connections.
  1. In the Data Connections dialog box, click Add.
  1. In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.
  1. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
  2. On the next page of the wizard, click Select Database.
  3. In the Select Data Source dialog box, browse to the location of your database.

 Note   If your database is stored in a network location, browse to the universal naming convention (UNC) (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) path of the location. Do not browse to the network location through a mapped network drive. If you use a mapped network drive, users who create forms based on this form template will search for the database from a mapped network drive. If the user does not have a mapped network drive, the form will not find the database.

  1. Click the name of your database, and then click Open.
  2. In the Select Table dialog box, click the primary table or query that you want to use, and then click OK.
  3. On the next page of the wizard, select the Show table columns check box.

By default, all of the fields in the table or query are added to the main data source of the form template.

  1. Clear the check boxes for the fields that you do not want to include in the secondary data source.

Add any additional tables or queries to the data connection.

ShowHow?

  • Click Add Table.
  • In the Add Table or Query dialog box, click the name of the child table, and then click Next. InfoPath attempts to set the relationships by matching field names in both tables. If you do not want to use the suggested relationship, select the relationship, and then click Remove Relationship. To add a relationship, click Add Relationship. In the Add Relationship dialog box, click the name of each related field in the respective column, and then click OK.
  • Click Finish.
  • To add additional child tables, repeat these steps.
  1. Click Next.
  2. To make the results of the query data connection available when the form is not connected to a network, select the Store a copy of the data in the form template check box.

Security  Selecting this check box stores the data on the user's computer when the form uses this data connection. If the form is retrieving sensitive data from this data connection, you might want to disable this feature to help protect the data in case the computer is lost or stolen.

  1. Click Next.
  2. On the next page of the wizard, type a descriptive name for this secondary data connection, and then verify that the information in the Summary section is correct.
  3. To configure the form to automatically receive data when the form is opened, select the Automatically retrieve data when form is opened check box.

Top of Page Top of Page

Step 2: Configure the form template to use the data connection

If you want the forms that are based on this form template to use this data connection after the user opens the form, you can add a rule (rule: A condition or action, or a set of conditions or actions, that automatically performs tasks based on events and values in the form.) to the form template that uses this data connection under a certain condition, or you can add a button to the form template that your users can click to use this data connection.

Add a rule

You can add a rule to the form template that serves to retrieve data from the query data connection whenever the condition for the rule is met. For example, you can add a text box to your form template that a user can fill out to get specific data from an external data source. You can then add a rule that uses the data connection whenever a user enters data in the text box.

The following procedure assumes that you have created a query data connection for your form template, and that you have configured a control (other than a button) on your form template to display the data from that data connection.

  1. If the form template has multiple views, click View name on the View menu to go to the view with the control where you want to display the data from the secondary data source.
  1. Double-click the control that you want to add a rule to.
  2. Click the Data tab.
  3. Under Validation and Rules, click Rules.
  4. In the Rules dialog box, click Add.
  5. In the Name box, type a name for the rule.
  6. To specify a condition when the rule should run, click Set Condition, and then enter the condition. The rule will run when the condition occurs. If you do not set a condition, the rule will run whenever the user changes the value in the control, and then moves his or her cursor away from that control.
  1. Click Add Action.
  2. In the Action list, click Query using a data connection.
  3. In the Data connection list, click the query data connection that you want to use, and then click OK to close each open dialog box.
  4. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Add a button

You can add a button control to your form template that your users can click to get data from the query data connection.

  1. If the form template has multiple views, click View name on the View menu to go to the view with the control where you want to display the data from the secondary data source.
  1. If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
  1. Drag a button control onto your form template.
  2. Double-click the button control that you just added to the form template.
  3. Click the General tab.
  4. In the Action list, click Refresh.
  5. In the Label box, type the name that you want to appear on the button on your form template.
  6. Click Settings.
  7. In the Refresh dialog box, do one of the following:
    • To receive the latest data from all external data sources with secondary data connections to this form template, click All secondary data sources.
    • To configure the button to receive the latest data from one external data source with a secondary data connection to your form template, click One secondary data connection, and then click a secondary data source in the Choose the secondary data source list.
  8. Click OK to close each open dialog box.
  9. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page Top of Page

 
 
Applies to:
InfoPath 2007