In this article
Overview
When you design a form template with a main data connection to a Microsoft SQL Server database, you may have to change the main data connection to another SQL Server database. For example, imagine that you design a form template and test it in an environment that uses a test database similar to your production database. During this test process, you design a form template that is based on a SQL Server database that your company uses for testing. But the production SQL Server database that users will use when you deploy the form template is on another server. Before you can deploy your form template, you need to change the main data connection from the test database to the production database.
When you change the database in your form template's main data connection, Microsoft Office InfoPath creates a main data source with 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 on the form template to the fields in the new data source. If the fields and groups in the new data source do not match the fields and groups in the old data source, InfoPath removes the data source binding from the controls. If InfoPath removes the binding from the controls, you need to remove the controls from the form template or bind the controls to other fields in the new data source.
After you change the main data connection, you should publish and test the form template to make sure that the main data connection to the database works correctly when users fill out forms that are based on your form template. You should also test to make sure that existing forms that are based on your form template still work as expected. When you complete your tests, you can allow users to start filling out forms that are based on this form template. Find links to more information about publishing a form template in the See Also section.
Before you begin
Before you change the main data connection, you need the following information from your database administrator:
-
The name of the server with the new SQL Server database.
-
The authentication required by the new database. The database can use either Microsoft Windows authentication or SQL Server authentication to determine how users can access the database.
-
Verification that the table names, field names, and relationships in the new database are the same as those in the old database. If you added table relationships when you first designed this form template, you need to add those same table relationships when you change the main data connection.
Change the main data connection
-
On the Tools menu, click Convert Main Data Source.
-
In the Data Connection Wizard, verify that the selected options are correct for this data connection, and then click Next.
-
On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
-
On the next page of the wizard, click Change Database.
-
In the Select Data Source dialog box, click New Source.
-
In the What kind of data source do you want to connect to list, click Microsoft SQL Server, and then click Next.
-
In the Server name box, type the name of the server that contains the SQL Server database.
-
Under Log on credentials, do one of the following:
-
If the database determines who has access based on the credentials used in a Microsoft Windows network, click Use Windows Authentication.
-
If the database determines who has access based on a specified user name and password that you get from the database administrator, click Use the following User Name and Password, and then type your user name and password in the User Name and Password boxes.
-
-
Click Next.
-
In the Select the database that contains the data you want list, click the name of the new database that you want to use, select the Connect to a specific table check box, click the name of the primary table, and then click Next.
-
On the next page of the wizard, type a name for the file that stores the data connection information in the File Name box, and then click Finish to save these settings.
If you plan to use other tables in the query data connection, you can add the other tables on this page of the wizard.
Add other tables
-
Click Add Table.
-
In the Select a child table to add list, 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, click the names of the related fields in both columns, 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.
-
To enable your users to submit their form data through another submit data connection that you will add later, click to clear the Enable submit for this connection check box to disable the submit data connection in the main data connection.
-
Click Finish.
Tip: If you need to change any query or submit data connections that are secondary data connections to the form templates, you can make these changes before going to the next step. By changing the secondary data connections now, you can test both the new main and new secondary data connections at the same time. Find links to more information on changing secondary data connections in the See Also section.
-
To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.
-
To publish the form template, on the File menu, click Publish.
-
Test the form thoroughly by opening a form that is based on this form template and filling it out. Verify that the form based on this form template works as expected.
