Create a Database Component Using ADO

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 is retrieved from the database. One way this is accomplished is by using a data presentation layer known as ActiveX Data Objects, or ADO. ADO allows HotDocs to communicate with a wide range of sources using OLE DB providers for each specific database program. The 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.

That said, in order to connect with a database, you must tell HotDocs to which database you want it to connect. This requires you to create at least one database component for your template, which is where information about the connection is stored—including information about the OLE DB provider you are using, the name of the database, and any security information that is required to access data in the database. Once you have created the database component and assigned a name to it, you must define a connection string, which is what HotDocs will use to connect to the database. This string contains information about the OLE DB provider, the location of the actual database file or server, and any other information needed to establish the connection.

To create a database component

1. At the template you want to connect to the database, click the Component Manager button. The Component Manager window appears.
2. Click the Components drop-down button and select Databases from the list. The list changes to show only database components.
3. Click the New Component button. HotDocs displays the Database Editor.
4. Type a name for the component in the Component name field.
5. 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 of answer to select. (See Add Resource Information to a Variable or Dialog.)
6. At the Properties tab of the Database Editor, click the Connection properties are defined by drop-down button and choose an ADO connection string.
7. Click the Edit button next to the Connection string field. The Data Link Properties dialog box appears.

The Data Link Properties dialog box is a standard Windows dialog box designed to set up a connection string. If you have specific questions about items in this dialog box, click the Help button located in the lower-right corner of the dialog box.

8. Select the OLE DB provider you need from the list of providers. For example:
   • If you are using a Microsoft Access database, select the Microsoft Jet 4.0 provider.
   • If you are using a SQL Server, select the Microsoft Provider for SQL Server.
   • If you are using an Oracle database, select the Oracle Provider for OLE DB.
   • For all other integrations, refer to the documentation for your specific database for help in identifying the correct provider.
9. Click Next. HotDocs displays the Connection tab of the Data Link Properties dialog box.
10. Depending on which OLE DB provider you selected, enter the required information about the data source to which you are linking, including selecting the actual database file.
11. When finished entering this information, click OK. The Database Editor appears again, showing the ADO connection string that will link your template to the database. (This connection string is encrypted when it is saved in the component file, but it will always appear in the Database Editor as plain text.)
12. Click the Field Map tab. The window changes to show how fields in the table will be linked to variables in the template.
13. At the Table name drop-down list, select the table to which you want to connect. 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.)

Database connections in earlier versions of HotDocs were maintained using a data presentation layer known as ODBC, or Open Database Connectivity. The ADO-based connection in HotDocs 11 allows you to continue using ODBC by using the OLE DB provider for ODBC, rather than a native OLE DB provider for the specific database. Maintaining this type of connection may be useful when a native OLE DB provider may either be incompatible or unavailable for you to use. (See Create a Database Component Using ODBC.) However, it is recommended that, where possible, you update all existing ODBC-based connections to use a native OLE DB provider for the database program you are using. (See Convert ODBC-based Connections to ADO.)

You can save ADO connection information in a Microsoft Data Link (.UDL) file. This may be useful if you plan to distribute your templates and databases to users whose systems may not be configured the same as yours. Because the connection information is saved in a file separate from the component file, it will make it easier for users without component-editing capabilities to update their connections. See Save ADO Connection Information in a Separate File for details.

If your connection to the database requires you to enter a user name, password, or other options, you can enter them at the Options tab of the Database Editor instead of including them in the connection string. See Specify a User Name and Password for a Database.