You can design a Microsoft Office InfoPath form template that works with a Microsoft Office Access database to either query data, or to query and submit data. You can design this form template based on a Microsoft Office Access 2007 (.accdb format) database or an Access database saved in an earlier version (.mdb format).
In this article
Overview
A form can have one primary data connection, called the main data connection, and that connection can optionally have one or more secondary data connections. You can use the main data connection to query or submit form data to an Access database. You can also use secondary data connections to query and submit data to external data sources, with some exceptions. For example, you can use a secondary data connection to an Access database so that it only queries the database. You cannot add a secondary data connection to a form that submits form data directory to an Access database.
When you design a form template that is based on an Access database, InfoPath creates a main data source with groups that contain query fields and data fields, and also creates a query data connection as the main data connection for the form template. These fields and groups correspond to the way that data is stored in the tables in the database.
Query fields contain the data that is entered into the form by a user to limit the query results to records that match the data in the query fields. When a form that is based on this form template uses the main data connection, InfoPath creates a query by using the data in the query fields. InfoPath then sends the query through the data connection. The database returns the results of the query back to the form through the data connection. The results of the query are put into data fields, which can be edited through controls on the form that are bound to those data fields.
Because the data structure of the query and the data fields must match the way that data is stored in the database, you cannot modify these fields or groups in the main data source. You can only add fields or groups to the root group in the main data source. Find links to more information about data sources in the See Also section.
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.
When InfoPath enables a submit data connection, users are allowed to submit the data that is stored in the data fields in the main data source to the database. You can customize the submit options for the forms that are based on this form template.
Compatibility considerations
You cannot design a browser-compatible form template based on an Access database.
Before you begin
Before you can successfully design a form template that is based on an Access database, you need the following information about the Access database:
-
The name and location of the database.
Note: Ensure that your database is in a network location that is accessible to your users.
-
The name of the table that will receive submitted data, if your form template allows users to submit forms to a database. You will use this table as the primary table when you configure the submit data connection.
-
The name of the table that supplies the results of the query sent to the database, if your form template will only query the database. You will use this table as the primary table when you configure the query data connection.
-
The names of any other tables that the primary table might require data from. In most cases, the table relationships are already established in the database. If you need to establish the relationships between the primary table and another table manually, you will need the related field names of both tables.
Design the form template
To design a form template with a query data connection, you first need to create the form template. When you create a form template that is based on a database, InfoPath creates a query data connection as the main data connection between the form template and the database. This process automatically creates the form template's main data source.
After you create the form template, you need to add a control to the form template and then bind that control to a field in the main data source. This allows your users to see the results of the query in the form.
Step 1: Create the form template
-
On the File menu, click Design a Form Template.
-
In the Design a Form Template dialog box, under Design a new, click Form Template.
-
In the Based on list, click Database, and then click OK.
The Data Connection Wizard starts
-
On the first page of the Data Connection Wizard, click Select Database.
-
In the Select Data Source dialog box, browse to the location of your database.
Note: If your database is stored in a network location, browse to the universal naming convention (UNC) path of that location. Do not browse to the network location through a mapped network drive. If you use a mapped network drive, users who create forms based on this form template will search for the database from a mapped network drive. If the user does not have a mapped network drive, the form will not find the database.
-
Click the name of your database, and then click Open.
-
In the Select Table dialog box, click the primary table or query that you want to use, and then click OK.
-
On the next page of the wizard, select the Show table columns check box.
-
By default, all of the fields in the table will be added to the main data source of the form template. Clear the check boxes for the fields that you do not want to include in the main data source.
Add any additional tables or queries that you want to use in the query data connection.
How?
-
Click Add Table.
-
In the Add Table or Query dialog box, click the name of the child table, and then click Next. InfoPath attempts to set the relationships by matching field names in both tables. If you do not want to use the suggested relationship, select the relationship, and then click Remove Relationship. To add a relationship, click Add Relationship. In the Add Relationship dialog box, click the name of each related field in the respective column, and then click OK.
-
Click Finish.
-
To add additional child tables, repeat these steps.
-
-
Click Next.
-
On the last page of the wizard, type a name for the main data connection. This name will appear in the Data Source list in the Data Source task pane.
-
If your form template meets the requirements in the Overview section, the Summary section on this page of the wizard indicates that InfoPath enabled the submit data connection in the main data connection.
-
To change the name for the submit data connection, type a new name in the appropriate box.
-
To enable your users to submit their form data through another submit data connection that you will add to the form template later, clear the Enable submit for this connection check box.
Note: If your form template does not meet the requirements in the Overview section, InfoPath disables the submit data connection, and the Enter a name for the submit connection box and the Enable submit for this connection check box are unavailable. If InfoPath disables the submit data connection, the main data connection for your form template will contain only a query data connection.
Step 2: Bind a control to the field
-
If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
-
Drag a control onto your form template.
-
In the Control Binding dialog box, select the field that you want to bind the control to.
Configure the submit options
If your form template and the tables that you selected in the Data Connection Wizard meet the requirements in the Overview section, InfoPath configures your form template to submit data by using its main data connection.
If you choose to use this submit data connection, InfoPath configures the form template so that users can submit their form data to the database, and also adds a Submit button to the Standard toolbar and a Submit command to the File menu on the form. InfoPath also configures the form template so that when users submit their forms, the form remains open and a message is displayed that indicates whether the form was successfully submitted. You can change the text that appears on the Submit button and also change the behavior of a form after a user submits it.
-
On the Tools menu, click Submit Options.
-
To change the name of the Submit button that appears on the Standard toolbar and the Submit command that appears on the File menu when users fill out the form, type the new name in the Caption box in the Submit Options dialog box.
Tip: If you want to assign a keyboard shortcut to this button and command, type an ampersand (&) before the character that you want to use as a keyboard shortcut. For example, to assign ALT+B as the keyboard shortcut for the Submit button and command, type Su&bmit.
-
-
If you do not want people to use a Submit command or the Submit button on the Standard toolbar when they fill out your form, clear the Show the Submit menu item and the Submit toolbar button check box.
-
By default, after users submit a form, InfoPath keeps the form open and displays a message to indicate if the form was successfully submitted. To change this default behavior, click Advanced, and then do one of the following:
-
To close the form or create a new blank form after the user submits a completed form, click the option that you want in the After submit list.
-
To create a custom message to indicate if the form was successfully submitted, select the Use custom messages check box, and then type your messages in the On success and On failure boxes.
Tip: Use a message in the On failure box to tell users what to do if they cannot submit their form. For example, you can suggest that users save their form and contact someone for further instructions.
-
If you do not want to display a message after the user submits a form, clear the Show success and failure messages check box.
-
-
