Add a data connection to a Microsoft SQL Server database

You can add a secondary data connection to your form template that queries a Microsoft SQL Server database. You cannot add a secondary data connection to your form template that submits form data to a database. If you want to use a secondary data connection to submit form data to a database, use a secondary data connection to a Web service that works with that database. Find links to more information about adding a secondary data connection to a Web service in the See Also section.

In order to add a secondary data connection to a database, you must do the following:

Step 1: Add a secondary data connection to the form template    This step creates the secondary data source with the appropriate fields and groups based on the way that data is stored in the database. You can also configure the form template to allow forms to use this data connection when the form first opens.

Step 2: Configure the form template to use the data connection    If you want your users to use this data connection after the form opens, you can add a rule or a button to the form template to allow users to get data from this data connection.

In this article

Overview

Before you begin

Step 1: Add a secondary data connection

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

Overview

When you create a form template that is based on an external data source, Microsoft Office InfoPath creates a main data connection to that external data source. InfoPath then creates the main data source for the form template based on the way that data is stored in the external data source. However, the external data source used in the main data connection may not have the values that you want to put in the controls on the form template. These values may exist in another external data source, such as another SQL Server database. To get values from another external data source, you can add a secondary data connection to your form template.

A secondary data connection is a data connection that you add to a form template. This data connection can be a query data connection or a submit data connection. You can add a query data connection to your form template only as a secondary data connection to a SQL Server database. You add this data connection only if you need data from an external data source other than the one in the main data connection. You cannot add a submit data connection to a SQL Server database as a secondary data connection.

When you add a query data connection to a database, InfoPath creates a secondary data source with data fields and groups that corresponds 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 the 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 may 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

Before you begin

Before you add a secondary data connection to a SQL Server database to your form template, you need the following information from your database administrator:

• The name of the server with the database that you will use with this form template.

• The name of the database that you will use with this form template.

• The authentication required by the database. The database can use either Microsoft Windows authentication or SQL Server authentication to determine how users can access the database.

• The name of the table that contains the data that you want to send to the form. This is the primary table. If you plan to use more than one table in the database, you need the names of those other, child tables. You also need the names of the fields in the child tables that have relationships to the fields in the primary table.

• Whether you can safely store the query results in the form for offline use.

Top of Page

Step 1: Add a secondary data connection

1. On the Tools menu, click Data Connections.

2. In the Data Connections dialog box, click Add.

3. In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.

4. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.

5. On the next page of the wizard, click Select Database.

6. In the Select Data Source dialog box, click New Source.

7. In the What kind of data source do you want to connect to list, click Microsoft SQL Server, and then click Next.

8. In the Server name box, type the name of the server with the database.

9. Under Log on credentials, do one of the following:

   • If the database determines who has access based on the credentials used in a Microsoft Windows network, click Use Windows Authentication.

   • If the database determines who has access based on a specified user name and password that you get from the database administrator, click Use the following User Name and Password, and then type your user name and password in the User Name and Password boxes.

10. Click Next.

11. On the next page of the wizard, in the Select the database that contains the data you want list, click the database that you want to use, select the Connect to a specific table check box, click the name of the primary table, and then click Next.

12. On the next page of the wizard, in the File Name box, type a name for the file that stores the data connection information.

13. To save these settings, click Finish.

14. Add any other tables that you want to use in the query 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.

15. Click Next.

16. 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 getting sensitive data from this data connection, you may want to disable this feature to help protect the data in case the computer is lost or stolen.

17. Click Next.

18. On the next page of the wizard, type a descriptive name for this secondary data connection. If you want 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 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 to get data from the query data connection whenever the condition for the rule is met. The following procedure assumes that you have created a query data connection for your form template, and that you have configured a control 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.

2. Double-click the control that you want to add a rule to.

3. Click the Data tab.

4. Under Validation and Rules, click Rules.

5. In the Rules dialog box, click Add.

6. In the Name box, type a name for the rule.

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

8. Click Add Action.

9. In the Action list, click Query using a data connection.

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

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

2. If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.

3. Drag a button control onto your form template.

4. Double-click the button control that you just added to the form template.

5. Click the General tab.

6. In the Action list, click Refresh.

7. In the Label box, type the name that you want to appear on the button on your form template.

8. Click Settings.

9. In the Refresh dialog box, click One secondary data source.

10. In the Choose the secondary data source list, click the secondary data source that is associated with the query data connection.

11. Click OK to close each open dialog box.

12. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page