Testimonial: This product saves me so much time! We create 180 new accounts each school year and this tool makes it so incredibly easy. It does all AD functions and creates the user directory and assigns permissions for all 180 users in less than 1 minute! Dave Griffin The Hotchkiss School http://hotchkiss.org read more...

Example 2 - Creating a form table to connect to a database

As part of a delegation project, you want to show a delegate user a table containing the phone numbers of employees in the IT department. These data are available in the database EmployeePhoneNrs.mdb. In the example below we will show you how a table can be created in UMRA to display these data.

The project created in this section can also be found in the directory \Tools4ever\User Management Resource Administrator\Example Projects\Forms\GetPhoneNumbers.ufp.

LDAP table - Linking UMRA to an MS-Access database containing phone numbers for all employees, listed by department

The MS Access database we need to link to, contains one table which is called EmployeePhone. This table holds the columns ID, PersNr, Employee, Department, Manager, Location, FirstName and Phone:

The link to the MS Access database is established through the Jet Engine, so there is no need to have MSAccess installed. In case you wish to explore the database yourself, you will find it in \Tools4ever\User Management Resource Administrator\Example Projects\Forms\EmployeePhoneNrs.mdb.

1. Start the UMRA Console application and connect to the UMRA Service: Select UMRA Service, Connect… and connect to the computer on which the UMRA Service is installed.
2. Create a new Forms project.
3. Right-click in the Forms window and choose the Add form field command.
4. Select the Table option.
5. Select the Generic table option and click the Configure button.
6. Click the Configure button once more.
7. Select the option Database query under Table type. A dialog box appears in which you can configure your database table. The following step is to define the database type and name.

   

8. Click the Database tab. The following dialog box will appear:

   

9. Click the Configure button to specify the database you wish to use. Select the option MS Access (Jet) database from the Database type list.

   

   Note - linking to a database is not limited to an MS Access database. You can connect to any database for which a OLE DB provider is available. If your database is not included in the standard OLE DB list of OLE DB providers, please check with your database provider.

10. Click the MS-Access (Jet) tab and browse to the file EmployeePhoneNrs.mdb. Click the Open button.

    

11. Click OK to return to the database setup window. We have now specified which database needs to be used. In order to specify which data we want to retrieve from the database, a database query must be specified. Click the Query tab. The following dialog box will appear:

    

12. Enter the following query:
13. SELECT Employee, Location, Phone FROM EmployeePhone WHERE Departments="IT"
14. This query will return all records in the columns Employee, Location and Phone of the EmployeePhone table (the table name you obtained in step 2) where the column Department is "IT".
15. Click the Run test tab and click the Test button. The following data should appear:

    

    In the Columns tab, the displayed columns can be changed.

16. Click the Columns tab.

    This window is used to configure which columns must be shown in the form. Here you also specify the variables that are passed to the UMRA Service when the end-user selects a table entry and presses a submit button. On the left side, the Available columns are shown. When the Run test was performed successfully in step 13, the actual column names will be shown here. Change the column width for column 1-3 as shown in . Exclude the columns 4-10 by selecting the column and clicking the left arrow (ß). Finally, click OK.

    

    Figure 23 - Configuring the columns to be displayed in the form table

17. Click OK. When you run the preview, the resulting table as shown in will be displayed.

    

    Figure 24 - Displaying database data in a form table

You have now succcessfully created a form table object to hold database content.