Using Secondary Data Sources

Secondary data sources are data sources that are outside of the data source that the form is connected to. For example, you may have an employee expense form that is attached to your accounting system and have the need to look up an employee’s contact details from another database or system. By connecting to a secondary data source, you can retrieve this information and use it in your own form.

Creating a form that accesses a secondary data source is a two-step process: create a connection to the data source, and bind a control to that data source. In this example, you are going to look up a customer number from a secondary data source and populate a drop-down list on a form.

To connect to a secondary data source, select Tools | Secondary Data Sources and click the Add button to open the Data Source Setup Wizard. In this example, you are going to walk through the process of creating a secondary data source from a SQL Server database, but the process is similar if you are connecting to an XML file or web service.

On the opening wizard page, select the Database option and then click Next to continue to the next wizard page, shown in Figure 10-5, where you can select the SQL server to connect to to retrieve your secondary data.

Figure 10-5: Data Source Setup Wizard

Next, click the Select Database button to choose your data source using a standard File | Open dialog box. You can select your connection from the list of available ODC files or create a new one.

Tip

For more information on creating an ODC file, refer to Chapter 5.

Once you have selected your data source, a list of available tables will appear and allow you to select the tables you want to use in your secondary data source.

Tip

If you want to connect to an Access database and want to connect directly to the database, you can use the Select Data Source dialog box to browse and select your Access (MDB) database file.

You are now ready to start working with the secondary data source you have just created—the Data Source Setup Wizard should now appear again, as shown in Figure 10-6, and show your database details as well as any tables you may have selected earlier.

Figure 10-6: Database details

Using this wizard page, you can add multiple tables to your data source and specify the relationship between them. To add an additional table to your data source, click the Add Table button to select a table from a list. The next step in the process is to specify the relationship between the two tables, which is also sometimes called a “join type” because it details how the tables are joined back together. To add a relationship between two tables, click the Add Relationship button.

This opens another dialog box that allows you to select the field in each table that will be used to define the relationship. After you select the fields, click the Add Relationship button to add this join. You can specify multiple relationships based on fields within your tables or remove them using the Remove buttons. When you are finished, click Finish to return to the Data Source Setup Wizard.

Tip

You can use the Remove Table and Modify Table buttons to remove tables or modify the relationships between tables. You can also click the Edit SQL button to use SQL to write your own queries.

The final step of the Data Source Setup Wizard is used to enter a name for your secondary data source and instructions on when to connect to this source. Next, you need to add a control to your form and bind the secondary data source to this control. In this instance, you are going to add a drop-down control to your form using the techniques we covered earlier in the book. Normally with a drop-down list, you could enter the values to appear, but in this case you are going to let the secondary data source do all the hard work for you.

Once you have added the drop-down control to your form, right-click the control and select Properties. In the Drop-Down List Box Properties dialog box, shown in Figure 10-7, in the List Box Entries area, select the Look Up in a Database, Web Service, or File radio button.

Figure 10-7: List box properties

Use the Data Source drop-down list to select the secondary data source you just created. Next, click the icon beside the Entries text box to select the field you want to display in the drop-down list. Once you click OK and preview your form, the drop-down list will be populated from the secondary data source and field you selected. This information will be stored with your form and submitted to the XML file or database when you submit or save your form.