Creating Forms from Existing SQL Server or Access Databases

One of the most popular features of InfoPath is its capability to push data back into existing SQL Server or Access databases. While XML provides a nearly universal standard for data exchange, Microsoft Access and SQL Server are the standard, respectively, for desktop and enterprise-class databases.

Using InfoPath, you can create forms that submit data directly back to a database hosted on either of these platforms. A typical scenario would be to use InfoPath forms to consolidate information from multiple users into a single database or table. You could then use this information for reporting purposes or to update other core systems.

In addition, this method also provides a flexible front end for simple data entry—while Access has its own method of creating forms for data entry, SQL Server has long relied on forms and applications created using Visual Studio and other programming tools to enter and maintain data. With the database features and functionality within InfoPath, you can quickly create InfoPath forms to replace these custom applications.

To start creating a form from an existing SQL Server or Access database, open InfoPath and select File | Design a Form to open the Design a Form view of the Task Pane. From the options under Design a New Form, select New from Data Source to open the Data Source Setup Wizard (refer to Figure 5-1). Select the Database option and click Next to continue to the next wizard page, shown in Figure 5-5, where you can select a SQL Server or Access database to use as the basis of your form. The steps that you follow next to create your form from an existing database vary depending on whether you want to use an Access database and connect directly to the database, use an ODBC connection to your Access database, or use a SQL Server database. The following sections describe how to connect to these different data sources.

Figure 5-5: Selecting the database to use as the data source for your form

Selecting a Data Source

Click the Select Database button to select your data source using the Select Data Source dialog box, shown in Figure 5-6.

Figure 5-6: Selecting the data source to use for your form

If you want to work with an Access database and want to connect directly to the database, use the Select Data Source dialog box to browse and select your Access (MDB) database file. After you have selected the database file, a list of available tables appears, similar to the one shown in the following illustration, which allows you to select the tables you want to use as the basis of your form.

Tip

If you are using this method to connect directly to your Access database, you can skip ahead to the section marked “Working with Tables,” as the next section details how to connect to an SQL Server.

If you are working with SQL Server or you want to use an ODBC connection to your Access database, you need to create an Office Data Connection (ODC) file for the data source.

Creating a New Data Connection

An ODC file contains information about the type of data source you are connecting to, its location, its username, and so on. To create a new ODC file, click the New Source button in the Select Data Source dialog box to open the Data Connection Wizard, shown in Figure 5-7.

Figure 5-7: Data Connection Wizard Welcome page

The first thing you need to do is select the type of data source you want to connect to—while the list you are presented with may show other data sources (Oracle, and so forth), the only two data sources that are valid (that is, the only two you can use successfully) are Microsoft SQL Server and ODBC DSN.

Note

If you select ODBC DSN, you need to select a data source name from the standard list of ODBC data sources configured on your computer, and the name you select must be pointing to a SQL Server or Access database. You will also be able to select the tables for your data source and then save your data connection file as described next.

In this example, select the Microsoft SQL Server option and click Next to continue to the next step of the wizard, shown in Figure 5-8, where you need to specify the name and credentials of the server you want to work with.

Figure 5-8: Providing logon information

Your database administrator should be able to provide you with the name of the server on which your SQL Server installation resides and tell you whether you are using Windows Authentication (meaning that your existing login and password from Windows will be used) or whether you need to enter a SQL Server–specific username and password. When you are finished entering your database details, click Next to continue to select the database and table that you want to work with, as shown in Figure 5-9.

Figure 5-9: Database and table selection

Using the drop-down list at the top of the wizard page, select the database where your data resides. A list of associated tables will appear in the pane immediately below the drop-down list. You can click to select a specific table or, if you are unsure of which table you want to use, you can uncheck the Connect to Specific Table option to create a generic connection to this database. (You will be prompted later for the table you want to use for your InfoPath form.)

After you click Next, the final step of the Data Connection Wizard is to save your ODC file, as shown in Figure 5-10. You can enter a name for your ODC file, as well as a description and some search keywords. By default, InfoPath stores these ODC files in a folder called My Data Sources, which appears under the standard My Documents folder, but you can save the file anywhere you like.

Figure 5-10: Saving your ODC file

Tip

In addition, you can also share ODC files with other users so that they don’t have to go through the same setup to access data sources you have already created.

Working with Tables

You are now ready to start working with the data source you have just created. The Data Source Setup Wizard should now appear again and show your database details at the top of the page, as shown in Figure 5-11, as well as any tables you may have selected earlier.

Figure 5-11: Selecting tables

Using this wizard page, you can add multiple tables to your data source and specify the relationship between them. For example, in an Order Entry form, you could have a customer table that contains all the customer-related information, including name, address, and so on, and then you could have another “child” table for the orders for that customer. You could use the two tables to create a custom Order Entry form within InfoPath that would write directly back to these tables.

To add an additional table to your data source, click the Add Table button to open the Add Table or Query dialog box, shown in Figure 5-12, and select a table from the list. Then click Next.

Figure 5-12: Adding a child table to your data source

The next step is to specify the relationship between the two tables (which is also sometimes called a “join type” because it details how the tables are joined back together). To add a relationship between two tables, click the Add Relationship button.

This opens another dialog that allows you to select the field in each table that will be used to define the relationship, as shown here.

How to...—Write Your Own SQL

If you are familiar with SQL (Structured Query Language), you can write your own SQL statements to extract information from your database instead of selecting the tables where the information resides. You need to be fairly experienced with SQL to use this functionality, because you will have to enter an entire SQL statement without the benefit of a user interface or query tools. To enter a custom SQL statement, click the Edit SQL button in the Data Source Setup Wizard to open the dialog box shown here.

Enter your SQL statement and use the Test SQL Statement button to check your SQL against the database. Although InfoPath won't be able to determine whether you are bringing back the correct data, it will be able to determine whether your SQL is well-formed and whether it will return a result set.

Before you actually enter your SQL statement here, try it out first in a Microsoft Access query or in the Query Analyzer tool that is available for SQL Server.

The fields that are present in the SELECT part of your SQL statement will be displayed as fields in your data set, and you can now use that result set as the basis of your InfoPath form.

For example, a Customer table and an Orders table could be used in one form— there would be a field in both of these tables called CustomerID that would specify the relationship between the two tables (for example, one customer has many orders). Using this dialog box, you would select the CustomerID field from both tables and click the OK button to add this join. You can specify multiple relationships based on fields within your tables or remove them using the Remove button. When you are finished, click Finish to return to the Data Source Setup Wizard.

Tip

You can also use the Remove and Modify buttons in the Data Source Setup Wizard to remove tables or modify the relationships between tables.

The final step of the Data Source Setup Wizard is used to select which view of your new InfoPath form you want to work with first. The Query view, shown in Figure 5-13, is used to query your data source for a record to return.

Figure 5-13: Query view of a form

For example, if you want to edit a certain customer’s details, you could query or search for that customer and then use the form you create in the Data View of your InfoPath form to edit the customer’s details, as shown in Figure 5-14.

Figure 5-14: Data Entry view of a form

Tip

You don’t necessarily need to even create a Query view for your form—you could simply create a form that creates new database records each time it is submitted.

Using the Design view, you can create your form just like you would any other InfoPath form, by adding fields, objects, and so on.

Tip

If you just can’t wait to get started, flip ahead to the next chapter, which starts the coverage of some more advanced form design techniques.