Introduction to data connections

A data connection is a dynamic link between a Microsoft Office InfoPath form and an external data source that stores or provides data for that form. A data source is a collection of fields and groups that define and store the data for a form. Controls are bound to fields and groups in the data source and display the data to users.

A form can have one primary data connection, called the main data connection, and it can optionally have one or more secondary data connections. Depending on your goals for the form, a data connection might query or submit form data to an external data source, such as a Microsoft SQL Server database or a Web service.

Overview of data connections

A data connection is a dynamic link between a form and a data source that stores or provides data for that form. A form can have one primary data connection, called the main data connection, and it can optionally have one or more secondary data connections. The main data connection defines the main data source of the form. An XML Schema describes how data is stored in the form's main data source. There can be only one main data connection for a form, and it is created automatically when you create a form template that is based on an external data source. You can create as many secondary data connections as you want when you design a form template.

Depending on your goals for the form, you can create a main or secondary data connection to an external data source, such as a Microsoft SQL Server database or a Web service. An external data source is a data store that sends data to or can receive data from a form that is based on your form template. InfoPath works with the following external data sources:

Microsoft Office Access database
Microsoft SQL Server database
Web service
Document library or list on a server running Microsoft Windows SharePoint Services
XML file

You can create three types of data connections to an external data source: query data only, submit data only, or both query and submit data. A query data connection receives data from a data source and stores that data in the form. A submit data connection sends data from the form to a data source, to an application on a Web server, to a document library on a server running Microsoft Windows SharePoint Services, or in an e-mail message. Query and submit data connections both receive data from and send data to a data source. When you create a data connection, you specify the type of data connection that you want. For example, you can create a data connection that does any of the following:

Queries data from a database
Submits form data in an e-mail message
Submits form data to an application on a Web server
Submits form data to a custom application that uses the XmlFormView control
Queries or submits data through custom programming code, such as C#, Microsoft Visual Basic .NET, or Microsoft JScript.
Uses connection settings stored in a data connection library on a server running Microsoft Office SharePoint Server 2007
Queries or submits data to Human Workflow Services on Microsoft BizTalk Server 2004 or BizTalk Server 2006

The type of connection that you use depends on your goals for the form and the type of external data source that you want to connect the form to.

Query data connections

A query data connection gets data from an external data source and stores that data in fields in the main or secondary data source of a form. You can then bind controls to those fields to display that data in the form. You can create a query data connection in the following ways:

Design a form template that is based on a database, Web service, or settings stored in a connection library. The structure of the external data source then defines the main data source for the form.
Modify an existing form template by adding a secondary data connection that queries a database, Web service, SharePoint list or library, or XML file.

When you design a new form template that is based on a database, Web service, or settings in a connection library, you create a query data connection that becomes the main data connection for forms that are based on that form template. You can define only one query data connection as the main data connection for a form template. If you need to query data from additional data sources, you can create secondary data connections that query data from those data sources. You will learn more about secondary data connections later in this article.

When you add a secondary data connection that queries data to your form template, you can specify whether InfoPath should use the data connection each time that the user opens the form or after specific events occur when the user fills out the form, such as clicking a button to display a list of options. To use the data connection after specific events occur, use one of the following methods:

Insert a button control that refreshes data in the form.
Add a rule to use the data connection when a condition on the form exists.
Write custom code for specific events.

Submit data connections

When users submit a form, data from that form is sent through a submit data connection to an external data source. You can configure that form template to allow users to send data to the following types of external data sources:

Microsoft Office Access database
Microsoft SQL Server database
Web service

You can also add secondary data connections that submit data in the following ways:

Submit form data to a document library on a server running Microsoft Windows SharePoint Services.
Submit form data in an e-mail message.
Submit form data to an application on a Web server.

You create a submit data connection in the following ways:

Design a new form template that is based on a database, Web service, or settings stored in a connection library on a server running Office SharePoint Server 2007. The structure of the external data source then defines the main data source for the form.
Modify an existing form template by adding a secondary data connection, and then configure the form template to allow users to submit their forms through this secondary data connection.

Tip: When you design a form template that is based on an Office Access or SQL Server database, InfoPath automatically configures a submit data connection if the database meets certain requirements. Find links to more information about those requirements in the See Also section. You can choose to disable the Submit feature if you want your users to submit their completed forms by using another type of submit data connection.

In most cases, you will configure one submit data connection as the main submit action for your form template. However, you can design your form template so that your users can submit their completed forms to multiple locations by using rules or custom code. For example, you can configure your form template to allow your users to submit their completed forms to a Web service as well as through an e-mail message. Typically, you configure the form template to submit all of the data in the form. However, if you use custom code to submit the form data or you are configuring the form template to allow the form to submit data to a Web service, you can submit a portion of the form's data.

Query and submit data connections

When you design a form template that is based on a database or a Web service, you can create a data connection that can get data from and submit data to the database or Web service. If you are designing a form template that is based on a database, the data connection can get or submit data. However, in order for forms based on this form template to submit data to the database, the following requirements must be met:

You are not designing a browser-compatible form template.
All of the tables in the main data connection must contain a primary key, unique constraint, or unique index.
All of the tables in the query must have a simple, hierarchical relationship.
None of the data fields in the main data source of the form can store a large binary data type.

You can learn more about these requirements in Considerations for connecting to external data sources later in this article.

A data connection that can get data from or submit data to a Web service is just like a query data connection and a submit data connection combined into a single data connection.

Top of Page

Working with the main data connection

The main data connection establishes a connection to the main data source of the form. An XML Schema describes the structure for storing data in the main data source of the form. You can design the data source by using the Data Source task pane, or you can let InfoPath create the data source automatically as you add controls to your form template. You can also design a form template based on an external data source, which in turn defines the structure of the main data source. A form template can have only one main data connection.

If the main data connection connects to an external data source, how you work with the main data connection depends on the type of data source that it connects to.

Database When you design a form template based on a database, InfoPath creates a main data connection that queries and optionally submits data to that database. If the form will be filled out only by using InfoPath and the database meets the requirements listed in the Considerations for connecting to external data sources section, InfoPath automatically creates a submit data connection and configures the form template to allow users to submit their forms. If the form will be filled out by using a browser, InfoPath does not create a submit data connection to the database. Data connections that submit data to a database are not supported in browser-compatible form templates.

Tip: If the form will be filled out by using a browser and you want to submit data to a database, you can do so by connecting the form to a Web service that submits data to the database.

Web service When you design a form template based on a Web service, you can create a main data connection that both queries and submits data, only queries data, or only submits data. If you choose to submit data, InfoPath automatically configures the form template to allow users to submit their forms. You can specify whether to submit some of the form data or all of the form data. If only some of the data is submitted, you can specify which fields in a particular group contain the data that should be submitted, depending on the requirements of the Web service.

When you design a form template based on a database or Web service, InfoPath creates a main data source with query fields, data fields, and groups that correspond to the way that data is stored in the database or Web service. A query field contains the value that is used in a query to retrieve data from a database by using the query data connection. The results of the query are put into data fields, which can be edited when the user opens the form. When the user submits the form, the data in the data fields is sent by using the submit data connection. Because the fields and groups in the main data source must match the way that the data is stored in the external data source, you cannot modify these fields and groups. You can, however, add fields or groups to the root field in the main data source.

To enable users to view, select, edit, or submit data in data fields, you can bind controls, such as text boxes and check boxes, to the fields in the form. If you want to query and display additional values that users can select as a choice, you can bind a control such as a list box to the query fields. If you want users to select a value from a list, you can bind a control such as a list box to a data field and then set the properties of the control to display values from a secondary data source or a repeating field in the main data source.

Top of Page

Working with secondary data connections

A secondary data connection connects a form to an external data source. Secondary data connections are useful when you need to query or submit form data to an external data source other than the external data source in the main data connection, or when you want users to submit data to multiple external data sources. You can create as many secondary data connections as you want.

A secondary data connection can query or submit data to the following types of external data sources:

Office Access database
SQL Server database
Web service
Document library or list on a server running Windows SharePoint Services
XML file

By using a secondary data connection, you can also enable users to submit form data to an application on a Web server, to a document library on a server running Windows SharePoint Services, or through an e-mail message.

If you create a secondary data connection that queries data, by default the form uses this data connection when the form opens. You can also use this data connection by configuring the form template in the following ways:

Add a refresh button to the form template that the user can click to send the query.
Add a rule to the form template to query the data source by using this data connection.
Add custom code to the form template that sends the query.

When you add a secondary data connection to a form, InfoPath automatically creates a secondary data source with data fields and groups that match the way that data is stored in the external data source. This helps ensure the integrity of the data. You cannot modify the fields and groups in a secondary data source.

Top of Page

Storing data for offline use

When you add a secondary data connection that queries data from an external data source, you can configure the connection to store the results of the query on the user's computer. This ensures that users can fill out the form if their computers are not connected to a network. This is called working offline.

If you configure a secondary data connection in this way, the query is sent to the external data source while the user's computer is connected to the network. The results of the query are then stored in the secondary data source of the form, and they appear in the form when the user fills out the form. Data is cached each time the user performs a new query, so that the data is kept up to date as much as possible.

InfoPath considers query parameters when deciding whether stored data is appropriate for a given query. When you create the data connection, you can choose whether to display a default set of stored data in the form, in case the data source is not available. You should consider whether the data from the query is appropriate for all of your users or whether the form should display data that is specific to each user. For example, if the data returned by the query is based on the user's identity or role, using a default set of data that was configured at design time instead of using query results may result in incorrect data for that user.

Top of Page

Considerations for connecting to external data sources

InfoPath supports data connections to the following types of external data sources:

Office Access database
SQL Server database
Web service
Document library or list on a server running Microsoft Windows SharePoint Services
XML file

The following sections provide information and considerations for connection to each of these external data sources.

Microsoft Office Access or SQL Server database

InfoPath supports data connections to Access databases and SQL Server databases. To connect to another kind of database, you can use a Web service that works with that database.

A form can query data from a database through either a main or a secondary data connection, and you can select any number of database tables for a connection. The first table that you select is the primary table. Any table that you subsequently select must contain a field that is related to the primary table. InfoPath attempts to set this relationship by matching the field names in the two tables. You can choose to use this relationship or add your own table relationship when you create the data connection.

A form can submit data to a database through the form's main data connection if the form template that the form is based on and the database meet the following requirements:

The form template is not a browser-compatible form template InfoPath will not create a submit data connection in the main data connection if you are designing a browser-compatible form template. To allow users to submit data in a form that is based on a browser-compatible form template, use a Web service that works with the database.
The left table in each pair of related tables in the main data source contains a primary key At least one of the relationships for every pair of related tables must include a primary key from the left table.
None of the data fields in the main data source of the form store a large binary data type InfoPath will disable the submit data connection if the query includes fields that can store a large binary data type, such as pictures, images, OLE objects, file attachments, the Office Access memo data type, or the SQL Text data type.

If the form template and the database meet all of these requirements, InfoPath creates the submit data connection and configures the form template to allow users to submit their forms. You can disable the submit data connection in the main data connection if you want users to submit their form data through a different data connection.

When you design a form template that is based on a database, InfoPath creates a main data source that contains query fields and data fields that match the way that data is stored in the database. InfoPath also adds Run Query and New Record buttons to the default view of the form template. When a user clicks the Run Query button, InfoPath sends a query with the data in the query field to the database. The New Record button clears any data currently in the form and sets controls that are bound to the data fields to the default value of the data field. When a user submits data to the database, any changes to the data in the data fields change the corresponding records in the database. If a user deletes data from a data field, the corresponding record is deleted from the database. Finally, any data that has been added to the data fields updates the corresponding fields or adds the corresponding records to the database.

If you want to connect a form to a database, and the main data connection has already been created for the form, you can add a secondary data connection that queries the database. When you add a secondary data connection, InfoPath creates a secondary data source with query fields, data fields, and groups that match fields in the database tables. You can select multiple database tables and establish table relationships just as you would for the main data connection.

When you create a secondary data connection that queries data, you can configure the connection to send a query each time the user opens the form, or you can do one of the following to send the query:

Add a button that users can click to refresh the data from the data connection.
Create a rule that sends the query by using the data connection.
Write custom code that sends the query when it is run.

Web service

A form can query or submit data to a Web service through either a main or a secondary data connection. InfoPath adheres to the following standards for connections to Web services:

Simple Object Access Protocol (SOAP) SOAP is the communication protocol that defines the XML messages that are used to communicate with the Web service.
Web Services Description Language (WSDL) WSDL is the XML Schema standard that is used to describe the location, communication protocols, and interfaces to the Web service. InfoPath can consume only document-literal style Web services.
Universal Description Discovery and Integration (UDDI) UDDI is the directory service that describes the Web services offered by a business.

When you design a new form template that is based on a Web service, InfoPath creates a main data connection to the Web service and then creates a main data source that contains query fields, data fields, and groups that match the XML Schema of the Web service.

If you configure the form template to use another operation in the same Web service or you want to use a different Web service altogether, you can add secondary data connections to the form template. When you add a secondary data connection that queries data, InfoPath creates a secondary data source with fields and groups that match the schema of the Web service. If you add a secondary data connection that submits data, you can configure the connection to send all or only some of the data in the form, depending on the parameters in the Web service.

When you create either a main or secondary data connection to a Web service, you can specify whether the connection queries data only, submits data only, or both queries and submits data. If the connection queries data, InfoPath adds the Run Query button to the form template. When a user clicks the Run Query button, InfoPath sends a query with the data in the query fields to the Web service. If the connection submits data, InfoPath enables the Submit feature for the form. When you configure a submit data connection, InfoPath determines what data is required by the Web service. Based on that information, you can specify what fields in the form template should submit their data to the Web service.

Document library or a list on a SharePoint site

A form can query data through a secondary data connection to a document library or a list on a server running Windows SharePoint Services. A form can also submit data to a document library on a server. You cannot connect to a library or list through a main data connection.

If you create a secondary data connection that queries data from a document library or list, InfoPath creates a secondary data source for the form that matches the columns in the document library or list.

If you create a secondary data connection that submits data to a document library and you want this submit data connection to be the main submit action for the form template, you must enable the Submit feature for the form template and associate it with the data connection by using the Submit Options dialog box (Tools menu, Submit Options command). By default, all of the data in the form is submitted through the submit data connection. If you want to submit only some of the data, you can do so by writing custom code for the form template.

XML file

A form can query data through a secondary data connection to an XML file. For example, you can save a Microsoft Office Excel worksheet as an XML file and then use a secondary data connection to query and display the data from that file in the form. You can also use a secondary data connection to query data from an XML file in order to retrieve data from an application on a Web server that returns XML, such as a Microsoft ASP.NET application, a Common Gateway Interface (CGI) script, or an application that uses the Internet Server Application Programming Interface (ISAPI).

Application on a Web server

You can create a secondary data connection that submits data to an application on a Web server, such as an ASP.NET application, a CGI script, or an application that uses ISAPI. To submit data to an application on a Web server, you must configure the form template to allow users to submit their form data and configure the submit options for the data connection by selecting Web server (HTTP) in the Submit Options dialog box (Tools menu, Submit Options command).

Note: A form can have only one data connection that submits data to an application on a Web server. You cannot configure a form template to allow forms to use a rule to submit data to an application on a Web server.

Connection settings in a data connection library

If several forms use the same or similar data connections, consider storing the settings for each data connection in a data connection file in a data connection library on a server running Office SharePoint Server 2007. A data connection file is an XML file that contains connection information for a single external data source and has an .xml or .udcx file name extension. When a user opens a form that connects to a data connection file, InfoPath uses the settings in that file to connect to the external data source. Advantages of using data connection files include the following:

Multiple forms can use the same data connection file, so there is no need to create the same data connection from scratch for each form.
If the location or connection settings for an external data source change, you need to update only the data connection file, not each form template.
The data connection file can contain alternative authentication information that can be used by the server when a user fills out a form by using a browser.
Forms that are filled out in a browser without a full trust security level can connect to a computer in another domain if all of the data connections in the form use data connection files.

To connect a form to a data connection file, the file must be stored in a data connection library on a server running Office SharePoint Server 2007 and it must follow the Universal Data Connection (UDC) version 2.0 file format.

Note: The UDC 2.0 file format is a superset of the version 1.0 format used by Microsoft Office FrontPage. InfoPath cannot use a data connection file in the version 1.0 format. Find links to more information about data connection libraries and data connection files in the See Also section.

Top of Page

Microsoft Office Tutorials

Friday, March 31, 2017