SolutionBase: Building database-integrated forms with InfoPath 2003

InfoPath 2003 allows you to create forms that are directly tied to back end databases. All it takes is designing the forms with back-end databases in mind. Here's how it works.

You can build forms with InfoPath 2003 that store data internally in the form. It's often more useful to tie a form directly into a database so the data can easily be integrated in your regular business system. For example, you might design a form for submitting purchase orders, creating and managing support incidents, submitting leave requests, and so on. You can tie a form to a Microsoft Office Access 2003 or Microsoft SQL Server database, to a Web service, to a Microsoft Windows SharePoint Services form library, in an e-mail message, or by using HTTP to a Web server. In this article, I explore how to create database-enabled forms with InfoPath 2003.

Choosing a data source

You have two direct options for integrating an InfoPath form with a database: Microsoft Access or SQL Server. InfoPath forms can submit data to these two databases without additional scripting. You can also submit data from InfoPath forms to other types of databases by scripting the form. Or, you can build the form to submit to a Web service connected to a backend database. I'll focus on Access and SQL Server here.

Existing InfoPath forms to which database connections have been added can query a database but not submit to the database. To create a submit-capable form, you must define the data connection when you create the form. The InfoPath team built this requirement into the product to ensure that the form matches the structure of the database. If the target database contains more than one table, those tables must be connected by key fields. In addition, the form can submit and query from the primary data source, but a secondary data source (added after the form is defined) can only be queried.

One potential drawback I see in InfoPath's database integration is the lack of support for submission to databases containing long data types such as memo and hyperlink fields. Conceivably you could script around this limitation, but it would be much better if InfoPath supported these data types directly. You can query data from such fields to include in a form, so a workable solution might be to use a combination of tables and fields structured to enable you to query all data and submit specific, supported fields. Or, simply design your database around InfoPath's requirements, if possible.

Now, let's take a look at how you create the form and define the main data source.

Defining the connection

When you create an InfoPath form, you can specify that it connect to a database. Specifying the database connection at this stage makes the specified database the primary connection and enables both query and submit.

Here's how to define a primary database when creating the form:

  1. Choose File, Design a Form.
  2. In the Design a Form pane, click New from Data Connection to start the Data Connection Wizard.
  3. Choose Database and click Next.
  4. Click Select Database to open the Select Data Source dialog box (Figure A).

Figure A

Choose an existing data connection or create a new one.

At this point you can specify a database in several ways:

  • Choose an existing source. InfoPath creates and ODC file for each database connection you've previously established or used. These files appear in the Select Data Source dialog box. You can simply click a file and click Open to choose it as the data source.
  • Select an existing Access database from a local folder or network share. Browse for and select the database file in the Select Data Source dialog box, then click Open. You can also choose files of other types, such as Excel spreadsheets, file DSNs, XML schema files, and others, but we'll focus on Access and SQL Server for now.
  • Create a new source. Click New Source to create a new data source. You can choose a SQL server database hosted locally or on a remote computer, specify a local ODBC DSN, connect to an Oracle database, or use the Other/Advanced option to choose from a wide selection of OLE DB providers.

Assume that you are creating a form to be used by multiple people, so we won't consider a local Access file. Instead, let's take a look at how you would connect to a local ODBC connection that you have already established. (If others will use the same form, they'll need the same DSN configured on their computers to connect to the database, wherever it's located.)

In the Select Data Source dialog box, click New Source to open the Data Connection Wizard. Choose ODBC DSN and click Next. The wizard displays the user and system DSNs configured on the local computer (Figure B). (File DSNs appear in the Select Data Source dialog box with a DSN file extension.)

Figure B

You can choose from user and system DSNs configured on the local computer.

When you click Next, InfoPath retrieves information about the target database (Figure C). At this point you can choose a specific table or clear the option and add tables later. If you clear this option, InfoPath prompts you to choose a table as soon as you finish defining the data source.

Figure C

InfoPath displays the tables in the target database.

The table you selected appears in the Data source structure field (Figure D), and you can select / deselect fields as needed.

Figure D

Select / deselect fields from the table as needed.

To change the sort order for the table, click Modify Table to open the Sort Order dialog box (Figure E). Here you can sort by up to three fields and specify whether the form can contain multiple records from the table.

Figure E

Use the Sort Order dialog box to sort the data and enable / disable multiple records.

Back in the Data Connection Wizard, you can edit the SQL statement for the connection, if needed. Click Edit SQL to open the Edit SQL dialog box (Figure F). Modify the statement as needed and click Test SQL Statement to check it. Then, click OK.

Figure F

Use the Edit SQL dialog box to modify the SQL syntax for the connection as needed.

Before you click Next to move to the next step in the wizard, you can add other tables from the selected database. Click Add Table, choose a table, and click Next. You must define a one-to-one or one-to-many relationship between the tables if none exists (Figure G). Click Add Relationship, select a field from each pane, and click OK. Add other relationships if needed, then click Finish.

Figure G

You must add a relationship between multiple tables in a connection if none exists by default.

If you need to change relationships or specify sort order from the secondary table(s), click the table and click Modify Table.

Click Next. InfoPath displays a summary (Figure H) that among other things indicates whether submit status is enabled for the connection. If not, the summary explains why (such as inclusion of a long data type in a table).

Figure H

The summary indicates whether submit status is enabled.

If you're connecting to a SQL Server database rather than access, the process is very similar but there are a few differences. After you select SQL Server from the Data Connection wizard and click Next, the wizard prompts you to specify the server and authentication method (Figure I). You can specify a NetBIOS host name, FQDN, or IP address. Next, choose the database hosted by the server from the drop-down list (Figure J). As with an Access database or DSN, you can connect to a specific table now or after the wizard is complete.

Figure I

Specify the SQL Server to use and authentication method.

Figure J

Choose the database to use for the connection.

Laying out the form

At this point the data source is defined and you're ready to start adding items to the form. Assume you've created a primary data connection to Microsoft's Northwinds sample database. You've added the Suppliers table as the primary table and the Products table as a secondary table. Let's lay out a form that you can use to view the products for a specific supplier and add new products.

When you create a database-enabled form, InfoPath adds two layout tables to a single view of the form (Figure K). The first is a query table, the second a data entry table. The former enables the user to query for records in the database and the latter displays data fields and enables the user to modify that data. Adding database fields is pretty much a simple click-and-drag process. You do, however, have some options as to how you add fields to the form. This example illustrates two of them:

Figure K

InfoPath adds two layout tables to the form automatically.

Start a new form and add the Northwinds database as the primary data source. Add the Suppliers table but exclude the hyperlink Homepage field (which would otherwise prevent submission). Click in the table containing the text "Drag data fields here". In the Data Source pane, right-click datafields and choose Section with Controls. InfoPath inserts repeating sections in the table.

Make the Supplier ID field read-only: Right-click the text box that contains @SupplierID, choose Text Box Properties, and click the Display tab. Check Read-only and click OK.

Click in the table containing the text "Drag query fields here". Expand queryFields in the Data Source pane, right-click q:Suppliers, and choose Controls in Layout Table. The layout table should now include the fields from the Suppliers table.

Next, add a form title and generally spruce it up with some additional explanatory text, table borders and shading, and so on (Figure L).

Figure L

Add shading, formatting, and other niceties to the form.

Save and close the form. Then, reopen the form to fill it out. Click in the top Supplier ID field, type the number 8, and click Run Query. Click Yes if you receive a security prompt. You should see the information for Specialty Biscuits as well as several product listings.

The result of your efforts is a quick and dirty form that you can use to query the database, update products and supplier information, and add new records. Naturally, it will take a lot more effort to create a professional-looking form. In most situations, you'll also need to create additional forms or add other tables and views to your forms to enable access to other aspects of the database you choose as the backend for your forms.

That's it!

At this point you have some background in database-integration with InfoPath. We'll explore some of InfoPath's other key features in upcoming articles.