Examples - 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\Generic\Tables\GetPhoneNumbers\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\Generic\Tables\GetPhoneNumbers\EmployeePhoneNrs.mdb.
Start the UMRA
Console application and connect to the UMRA
Service: Select UMRA Service,
Connect&ldots; and connect
to the computer on which the UMRA Service
is installed.
Create a new Forms project.
Right-click in the Forms window and choose the
Add form field command.
Select the Table
option.
Select the Generic
table option and click the Configure
button.
Click the Configure
button once more.
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.
Click the Database
tab. The following dialog box will appear:
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.
Click the MS-Access
(Jet) tab and browse to the file EmployeePhoneNrs.mdb.
Click the Open button.
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:
Enter the following query:
SELECT
Employee, Location, Phone FROM EmployeePhone WHERE Departments="IT"
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".
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.
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
under Available columns. When
the Run test has been performed
successfully in step 13, the actual column names will be shown here. Change
the column width for column 1-3 as shown in the figure below.. Exclude
the columns 4-10 by selecting the column and clicking the left arrow (<--).
Finally, click OK.
Click OK.
When you run the preview, the resulting table as shown in the figure below
will be displayed.
You have now succcessfully created a form table object to hold
database content.
