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.

Overview

A secondary data connection 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 that contains data fields and 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 Note: 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

Compatibility considerations

You cannot add a data connection to an Access database to a browser-compatible form template.

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.
The name of the table or query that will supply data to forms that are based on this form template. You will use this table or query as the primary table or query when you configure the query data connection.
The names of any other tables or queries that supply additional data to the primary table or query. In most cases, the table relationships are already established in the database. If you have to manually establish the relationships between the primary table or query and another table or query, you will need the related field names of both tables or queries.

Top of Page

Step 1: Add a secondary data connection

On the Tools menu, click Data Connections.
In the Data Connections dialog box, click Add.
In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.
On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
On the next page of the wizard, click Select Database.
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) 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.
Click the name of your database, and then click Open.
In the Select Table dialog box, click the primary table or query that you want to use, and then click OK.
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.
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.

How?
1. Click Add Table.
2. 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.
3. Click Finish.
4. To add additional child tables, repeat these steps.
Click Next.
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 Note: 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.
Click Next.
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.
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

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 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.

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.
Double-click the control that you want to add a rule to.
Click the Data tab.
Under Validation and Rules, click Rules.
In the Rules dialog box, click Add.
In the Name box, type a name for the rule.
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.
Click Add Action.
In the Action list, click Query using a data connection.
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.
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.

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.
If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
Drag a button control onto your form template.
Double-click the button control that you just added to the form template.
Click the General tab.
In the Action list, click Refresh.
In the Label box, type the name that you want to appear on the button on your form template.
Click Settings.
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.
Click OK to close each open dialog box.
To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page

Microsoft Office Tutorials

Monday, January 14, 2019