With Microsoft Office InfoPath, you can design a form that is connected to a Microsoft Office Access database. By using these two programs together, you get all of the data collection advantages of InfoPath along with the data storage advantages of Access.

InfoPath advantages include forms that can be filled out while users are offline, a rich set of controls that make it easy to design and fill out forms, and data validation rules that are automatically imported when the form is connected to the Access database.

When you connect an InfoPath form to an Access database, you can choose whether you want to set up the database as the form’s main data source or secondary data source. If you want to edit and add records to the database by filling out fields in the InfoPath form, you should set up the database as the form’s main data source.

To use an Access database as the primary data source for an InfoPath form, you must start with a new form. You cannot modify an existing form to add a connection to a primary data source. Use the following procedure to create a new InfoPath form based on the sample database in Access:

1. From the File menu under New, click Design a Form.
2. In the Data tab of the ribbon, click Data Connections and then push the Add button.
3. Select receive or submit data and then select Database as the data source (Microsoft SQL Server or Microsoft Office Access only), and then click Next. Remember that you can always use our SQL server hosting as well to keep all your data connected and stored in the cloud rather than in a local MS Access file.
4. Use the browser to find and Select Database.
5. In the Select Data Source dialog box, browse to a valid Access database and select it.
6. Then Select the Table that you would like to collect data via InfoPath

In the screenshot above, I have selected the Authors Table from my Books Database. By selecting the Authors table first, I have setup this table as the primary table for the data connection. I can continue to add other tables via this data connection method to MS Infopath. Additional tables create a one-to-many relationship. In Access, this relationship is defined by using a key field, which is a field that associates the records in one table with the records in another table.

When connecting your form to multiple tables in a database, InfoPath requires that the tables be connected by key fields. Also, the primary table in the data connection must have either a one-to-many or a one-to-one connection with any additional tables that you add.

By default, InfoPath connects to every field in a table. However, you might want to exclude a field, either because it uses a data type that InfoPath cannot connect to or because you do not want to work with a certain field’s values in your form.