Change the main data connection to another microsoft access database

When you design a Microsoft Office InfoPath form template that is based on a Microsoft Office Access 2007 (.accdb format) database or an Access database saved in an earlier version (.mdb), InfoPath automatically creates a main data connection to that database. If, at a later date, the location of your Access database changes, such as if you move the database to another location, you will need to change the main data connections of the form template. This article explains how to change the main data connection in your InfoPath form template to point to a different Access database.

Find links to more information about changing secondary data connections to other external data sources in the See also section.

In this article

Overview

Before you begin

Step 1: Change the main data connection in the form template

Step 2: Select the database tables

Step 3: Preview, publish, and test the form template

Overview

To change the form template's main data connection to a new Access database, you use the Data Connection Wizard to create a new main data connection. When you create the new data connection, InfoPath creates a new main data source that contains fields and groups that correspond to the way that data is stored in the new database. If the new database stores data in the same way as the old database, InfoPath automatically binds the existing controls in the form template to the fields and groups in the new main data source.

If the fields and groups in the new main data source do not match the fields and groups in the old data source, InfoPath removes the data source bindings from the existing controls. If those bindings are removed, you will then need to bind the controls to other fields and groups in the new main data source if you want to continue to use these controls, or you can remove the controls from the form template.

After you change the main data connection, you should publish the form template and test that the forms based on this form template still work in a test environment. After you complete your tests, you can then allow users to start filling out new forms that are based on this form template.

Find links to more information about publishing a form template in the See also section.

Top of Page

Before you begin

Before you change the main data connection, you will need the following information from your database administrator:

• The location of the new Access database.

  Note: If the new Access database is stored in a network location, make sure that the network location is accessible to your users.

• Verify that the table names, field names, and relationships in the new database are the same as those for the old database. If you needed to add table relationships when you first designed the form template, you will need to add those same table relationships when you change the main data connection.

Top of Page

Step 1: Change the main data connection in the form template

1. On the Tools menu, click Convert Main Data Source.

2. In the Data Connection Wizard, verify that the selected options are correct, and then click Next.

3. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.

4. On the next page of the wizard, click Change Database.

5. In the Select Data Source dialog box, browse to the location of the new Access database, and then click Open.

Top of Page

Step 2: Select the database tables

1. In the Select Table dialog box, click the name of the primary table or query, and then click OK.

2. If you are using other tables or queries in the database to supply data to the form, add those additional tables or queries.

   How?

   1. Click Add Table.

   2. In the Add Table or Query dialog box, in the Select a child table to add list, click the name of the table or query, and then click Next.

      The related fields between this table or query and the primary table or query are shown in the Connecting fields box, in the Edit Relationship dialog box. Verify that you want these fields in your form template.

   3. To remove the existing relationship, click the relationship in the Connecting fields list, and then click Remove Relationship.

   4. To add additional related fields, click Add Relationship, and then in the Add Relationship dialog box, click the fields in both columns that you need.

   5. Click OK, and then click Finish.

   6. To add additional tables or queries, repeat these steps.

3. Click Next.

4. On the next page of the wizard, type a name for the main data connection, and then click Finish.

   Note: If you need to change any secondary data connections to the form template, make these changes before going to the next step. By changing the secondary data connections now, you can test both the new main and secondary data connections at the same time. Find links to more information on changing secondary data connections in the See also section.

Top of Page

Step 3: Preview, publish, and test the form template

1. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

2. To close the preview window, click Close Preview on the Standard toolbar.

3. To publish the form template, click Publish on the File menu, and follow the steps in the Publishing Wizard that starts.

4. Test the form thoroughly by opening a form based on this form template and filling it out. Verify that the form based on this form template works as expected.

Top of Page