Create a Database Component Using ODBC

In order for programs such as HotDocs to communicate with certain database programs, the database must understand the command HotDocs issues to retrieve data from it. Likewise, HotDocs must understand how to work with the data that gets retrieved from the database. This is accomplished by using a data presentation layer known as ADO, or ActiveX Data Objects. The functionality behind ADO allows HotDocs to communicate with a wide range of database programs using OLE DB providers for each specific database program. In essence, an OLE DB provider acts as a translator between the specific database and HotDocs—it reads information from the database and tells HotDocs how to interpret the data so HotDocs can use it.

Many database programs and/or clients include their own native OLE DB provider (see Create a Database Component Using ADO), but in some situations, an OLE DB provider may either be incompatible or unavailable for you to use. (Click here for a list of supported databases.) In such situations, you can use the OLE DB provider for ODBC (Open Database Connectivity). This type of provider allows you to link your template to the database using an ODBC data source.

To connect to a database using the OLE DB provider for ODBC, you must first make sure you have a database driver installed that is compliant with ODBC Level One or higher standards. Drivers are often installed when you install the database program. If the driver you need is not installed, you should contact the database program provider.

Once you know the correct driver is installed, you must create a data source, which sets up a connection between the database driver and the database so that you can retrieve information from it. You can create one data source to associate a single driver with several database tables, as long as the tables are associated with one database.

Then, once you set up the data source, you must create a database component that uses your data source. HotDocs will use the data source to link to the database table that contains your information. You can use Component Manager to create a database component.

To create an ODBC data source and link it to a database component

  1. Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator dialog box appears.
  2. With the Data Source Administrator dialog box displayed (and the User DSN or System DSN tab selected), click Add. The Create New Data Source dialog box appears.
  3. Select the driver you need and click Finish. The ODBC Setup dialog box appears.
  4. Type a unique name for the data source in the Data Source Name field.
  5. If the ODBC Setup dialog box includes database version options, select the version you are using.
  6. Specify the database (or the folder containing the database tables) you want to use. The procedure varies depending on the driver you selected.
  7. At the ODBC Setup dialog box, click OK to return to the Data Source Administrator dialog box. The new data source is added to the list of data sources.
  8. Click OK at the Data Source Administrator dialog box and close the Control Panel window (if it's open).
  9. Open the template you want to connect to the database. (See Edit a Template.)
  10. Click the Component Manager button in the HotDocs toolbar. The Component Manager window appears.
  11. Click the Components drop-down button and select Databases from the list. The list changes to show only database components.
  12. Click the New Component button. HotDocs displays the Database Editor.
  13. Type a name for the component in the Component name field.
  14. Optionally, perform the following tasks:
    • In the Title field, enter a title for the database component. The title will be used in the interview outline and dialog title bar.
    • In the Prompt field, type the information about the database table you want the user to see. This information will appear above the database table in the assembly window. (See Create a Prompt for a Variable.)
    • Click the Resource tab and provide a resource option. A resource helps the user know what type answer to select. (See Add Resource Information to a Variable or Dialog.)
  15. From the Connection properties are defined by drop-down list, select an ODBC data source.
  16. From the ODBC Data Source Name (DSN) drop-down list, select the name of the data source you created earlier.
  17. Click the Field Map tab. The window changes to show how fields in the table will be mapped to variables in the template.
  18. At the Table name drop-down list, select the name of the table you want to use. The information from that table appears in the Field Name column.

Once you have created the database component and associated a table with it, you must link variables in the template to fields in the database table. (See Link Variables to Database Fields). You also must designate a unique field in the table as the key field, which will allow HotDocs to remember which record the user selects during assembly. (See Remember Selected Records.)

If your connection to the database requires you to enter a user name, password, or other options you do not wish to include in the ODBC data source, you can enter them at the Options tab of the Database Editor. See Specify a User Name and Password for a Database.

In order for HotDocs to remember which records your users have selected during assembly, you must assign a key field. For information on doing this, see Remember Selected Records.