Express Essentials: Connecting InfoPath to SQL Server Express

In the past several issues of Express Essentials, I've covered connecting the various products in the Microsoft Office Suite to SQL Server Express. To wrap up that Office coverage, in this issue I show you how you can use the forgotten member of the Office family, Microsoft Office InfoPath 2007, as a forms-based front end to a SQL Server Express database.

The first step in connecting InfoPath to SQL Server Express is designing a form. To create an InfoPath form that connects to SQL Server Express, open InfoPath, then select the Design a Form Template to launch the Design a Form Template Wizard. On the first screen, make sure that the Form Template radio button is selected, select Database from the "Based on" list, then click OK to start the Data Connection Wizard.

In the Data Connection Wizard, click the Select Database button to display the Select Data Source dialog. If you've never before connected InfoPath to a database, this page will be blank. Click the New Source button, then click Next. In the "What kind of data source do you want to connect to" list, select Microsoft SQL Server and click Next to display the Connect to Database Server dialog. If you're using the default SQL Server Express installation settings, enter SQLEXPRESS at the server name prompt, select Use Windows Authentication, and click Next.

When the Select Database and Table dialog appears, use the "Select the database that contains the data you want" list to select the SQL Server Express database that you want to use. For example purposes, we'll select the Northwind database, which populates the "Connect to a specific table" list with all the tables from the Northwind database. Select the Customers table, then click Next and Finish.

From the Customers table, select the columns that you want to use. For this example, let's accept the default, which selects all the columns, then click Next. In the next dialog, which lets you name the connection, change the name Main Connection to Northwind, then click Finish.

The InfoPath design surface is displayed with the Data Source window appearing on the right side of the design window. By default, the design surface will show a New Record section and a Run Query section. From the Data Source window, select the q:Customer data source, drag it to the Run Query section, then select "Section with Control" from the pop-up menu. In the New Record section, click d:customer, drag it to the design surface, then select Horizontal Repeating Table from the pop-up menu. You could continue to customize the form, but for this example, we'll just click the Save icon, which saves our work and completes the InfoPath form that connects to SQL Server Express.

Before you run the form, you need to publish it. When you select File, Publish, the Publishing Wizard asks where you want to publish your form and what you want to name it. You can save the form to a SharePoint site, to an email message, to a network share, or as a Visual Studio template. After publishing your form, you have a new InfoPath front end to the SQL Server Express Northwind Customers table.