Show All

Design a form based on a database

When you design a form that is connected to a database, Microsoft Office InfoPath 2003 builds a data structure for your form based on that database. The resulting form can then be used to submit data to and query the database. You can work with the form in many of the same ways as when you design a new, blank form— you can insert and lay out controls, add or remove data validation and conditional formatting, and publish the form.

Note  Without the use of custom code, InfoPath can connect only to Microsoft SQL Server and Microsoft Access databases.

1. On the File menu, click Design a Form.
2. In the Design a Form task pane, click New from Data Connection.
3. On the first page of the Data Connection Wizard, click Database (Microsoft SQL Server or Microsoft Access only), and then click Next.
4. On the next page of the wizard, click Select Database.
5. In the Select Data Source dialog box, browse to the SQL Server or Access database you want to base the form on, and then click Open.

   Note  If you are connecting to a SQL Server database, you may need to click New Source, next to the File name box in the Select Data Source dialog box, and then follow the steps in the wizard to create a remote data source connection to the SQL server.

6. If there is more than one table in your database, click a primary table for your form in the Select Table dialog box, and then click OK.
7. Add any additional tables.

   How?

   1. In the Data source structure list, click the table you want to add the new table to, and then click Add Table.
   2. In the Add Table or Query dialog box, click the table you want to add, and then click Next.
   3. In the Edit Relationship dialog box, make sure that the connecting fields are correct.
   4. If you need to add a set of connecting fields, click Add Relationship, select the two fields that are equal when a record in one table is associated with a record in another table, and then click OK.
   5. In the Edit Relationship dialog box, click Finish.
8. In the Data Connection Wizard, click Next.
9. On the next page of the wizard, type a name for the data connection, and then click Finish.
10. To add controls to the form, right-click a field or group in the Data Source task pane, and then click the control that you want to insert on the shortcut menu.

Notes

• When you design a form that is connected to a database, the resulting data source consists of query fields and data fields. Query fields should be used to create controls that allow users to enter values that they can query the database with. Data fields are filled in with the query results, and also allow users to edit existing records and add new ones.
• By default, InfoPath creates the Run Query button and the New Record button in the form when you connect it to a database. When clicked, the Run Query button queries the database based on the values in the form's query fields. The New Record button resets the form's data field values to their initial state.
• By default, InfoPath enables the Submit command on the File menu for a form that can submit data to a database. Clicking the command allows users to submit the data in their completed form to the database.
• Because the data structure for the form must match the database, you cannot automatically create the data source when inserting controls. In addition, you cannot modify existing fields or groups in the data source, and you can only add fields or groups to the root group in the data source.

Note  The information in this topic may not apply if you are working with a form designed using Microsoft Office InfoPath 2003 without the service pack installed. Learn more