A mix of Access 97, Exchange, and Outlook turned a stand-alone
program into an enterprise application.
Keeping Track of Candidates
A mix of Access 97, Exchange, and Outlook turned a stand-alone
program into an enterprise application.
- By Keith Lubell
- January 01, 1999
A well-known axiom says that engineers create solutions
to engineering problems. However, often Ive found
that engineering solutions are sometimes created without
problems to be solved. Littered along the highway of technological
progress are many such un-problemed solutions.
Quadraphonic 8-track tapes, microchip-driven toasters,
and scratch-and-sniff movies come to mind. Many an engineers
heart was broken over such failures.
In this article, I discuss a scenario where I solved
a real problem with a hybrid solution of Access 97 and
Exchange Server 5.5 public folders with Outlook 98 forms,
using the strengths of each platform.
A Question of Sharing
The client with the problem to be solved was my own company.
Were a computer consulting firm with five U.S. locations.
We wanted an automated business process for tracking engineering
and programming candidates. Were flooded with hundreds
of resumes every quarter. To manage the recruitment process
efficiently, we developed an Access 97 database to track
candidates, store resumes, and generate e-mail responses
and form letters. Technical recruiters use this database
daily and have found that it makes the recruitment process
But each database was an island, and only users in that
location could access its information. The technical recruiters
in the outlying locations need to share some of the information
about candidateslike the status of applicationswith
the human resources department, which is centrally located.
Access 97 limited our ability to share this information
across the company. We could have used Access 97s
replication functionality; but replication required manual
intervention as well as a high bandwidth connection between
the locations, which currently doesnt exist.
To solve this problem, we used the Microsoft Exchange
public folders and Microsoft Outlook forms. Public folders
provide a reliable, fault-tolerant, and automatic replication
mechanism to share data across all locations in the company.
Outlook forms published to the public folder provide a
32-bit distributed form for viewing the shared data.
The company has a well-tuned Exchange e-mail system with
sites connected via dynamic RAS connectors. The company
uses a hub and spoke architecture with the Manchester,
New Hampshire office as the hub and outlying district
offices in New York, New Jersey, Massachusetts, and Washington
state as the spokes. Each outlying district has at least
one technical recruiter, and HR is located at the hub.
Most of the outlying districts use either dynamic RAS
connectors over low bandwidth phone lines or X.400 connectors
over a Virtual Public Network (also low bandwidth). Each
site contains at least one Exchange server. The contents
(mail items, forms, permissions, and views) of certain
public folders are marked for replication to all locations.
One folder contains candidate information. Figure 1 illustrates
the Exchange topology of the company.
|Figure 1. The Exchange topology
of the company includes a hub site in New Hampshire
and four spoke sites elsewhere in the U.S.
Exchange replication is easy to configure, as long as
you have Exchange administrator privileges. Each public
folder can be configured independently, through the Candidate
Status properties Replicas tab. (See Figure 2.) A couple
of notes: The Waltham and Washington sites arent
included in the replication; and the public folder were
replicating is called Candidate Status.
|Figure 2. Replication for an
Exchange public folder gets defined at the Candidate
Status properties Replicas tab.
Access 97 is a great tool for doing rapid application
development. To jumpstart the creation of the recruiters
candidate database, we used the sample Contact Manager
database created by the Database Wizard as a starting
point. We extended it by adding new tables to track skill
sets and certifications, creating new fields in existing
tables to be able to embed resumes in OLE fields, and
additional recruitment information. (See Figure 3.).
|Figure 3. The database structure
behind the Access application.
The main form, shown in Figure 4, was modified to include
a variety of new functions. Resume functions include pasting
resumes from the Clipboard to the database and scanning
those resumes for skills to track the candidates
skill set. Outlook functions enable the recruiters to
send e-mail messages to candidates, make appointments
in the recruiters Outlook calendar for interviews,
and post candidate status information to the candidate
status public folder. Its this last function that
well focus onits what makes this stand-alone
program into an enterprise application.
|Figure 4. The interface for the
Currently, the application is written to update information
in the public folder whenever the user clicks on the Update
HQ button. (Alternatively, we could have made the
update function fire whenever the candidate information
is added or changed by the user.) So the interesting code
that updates the public folder resides in the click event
of the button. (See Listing 1.)
The public folder gets
updated when a user clicks on the Update
HQ button. Heres that click event.
Private Sub cmdUpdateHQ_Click()
On Error GoTo Err_cmdUpdateHQ_Click
Dim strNotesl As String
Dim strStatusl As
Dim strSourcel As
Dim strTypel As String
information into Strings from Form
strNotesl = GetSkills(ContactID.Text)
strStatusl = cbtStatus.Text
strSourcel = cbtContactSource.Text
strDistrictl = New
strTypel = ContactTypeID.Text
strReferrall = ReferredBy.Text
Call a routine to Update the
If Not UpdateHQOLE(strNotesl,
strCandidate Namel, _
strSourcel, strReferrall, strDistrictl,_
Failed to Post Update because:
Error Resume Next
This click event handler reads information from the Access
form fields and places them into strings. In order for
Visual Basic for Applications to read data from a form
field, that field must have the focus. Once weve
gathered all the candidate status information that we
wish to publish to the Exchange public folder, we call
the UpdateHQOLE function to do the posting.
(See Listing 2.)
UpdateHQOLE does the actual
posting of information.
UpdateHQOLE(strNotes As String, _
As String, strStatus As String,_
strSource As String,
strReferral As String, _
As String, strType As String) As Boolean
On Error GoTo Err_UpdateHQOLE
Dim objOLEApp As Object
= fldOLEfolder.Folders(All Public
= fldOLEfolder.Folders(Human Resources)
for existing Item on Candidate
= & _
strCandidateName & Chr(34))
found create new Item, otherwise we
already have it
Is Nothing Then
to the User Properties of the Outlook
Status) = strStatus
Type) = strType
form to the Public Folder
On Error Resume Next
to Post Update because: &
First, we declare a bunch of variables. objOLEApp, declared
as an Object variable, holds a reference to the Outlook
98 ActiveX .exe file. This variable is a starting point
to reference the rest of the type library, allowing us
to perform ActiveX automation using the Outlook 98 object
model. For this to work, the recruiter must have Outlook
98 installed on his or her machine, and the Access application
must have a reference set to the Outlook 98 type library
(under Tools | References).
We use NspOLEnamespace, which is declared as an Outlook
NameSpace variable, to navigate through the tree of folders
presented to us by Outlook.
fldOLEfolder references MAPI folder objects, and itmOLECandidate
references the custom Outlook form we created to hold
the candidate data.
First, the procedure creates an instance of Outlook 98
and returns a reference to that application. Next, we
create a namespace object from the application. From this
namespace object we can get a reference to any of the
top-level folders. In this case we get a reference to
the top of the public folders tree, then iterate down
the tree to the folder we want. Theres no way to
jump directly to a given folder. Look at the figure of
the public folder tree in Figure 5 and see how it correlates
to the code that iterates down the tree. At this point
in the code execution, fldOLEfolder contains a reference
to the folder where we want to post our candidate data.
|Figure 5. The application moves
through the public folder tree to the folder where
candidate data gets posted.
The next step is to search for an existing item on the
candidate. In this application, weve made a rule
that theres to be only one item (a custom Outlook
form) per candidate. Remember that Exchange public folders
provide a non-relational information store, so our code
must enforce the uniqueness.
Note that we use the Find method of the folders
items collection. This items collection contains references
to all the mail items in the folder. The Find method works
like the DAO recordset find method; it finds any items
matching the criteria weve stated. In this case,
it searches a user-defined field that contains the candidate
name. A potential error could occur if we changed a candidates
name. In that case two items will exist for the candidate,
one for each name. This is due to the fact that an Exchange
Public folder is a non-relational information store; we
cant create true unique keys. If an item isnt
found for the candidate were posting, the reference
to the post item is nothing. In that case we simply create
a new post item.
At this point we now have a reference to the post item
for the candidate. In this application the post item is
simply a custom Outlook 98 form that weve created
based on the standard post item. The next step is to populate
the candidate status item with data gathered from the
Access database. The item has some custom-defined user
properties, where we place most of the information, and
we use the standard post forms body to store a string
with the skill set of the candidate.
All thats left is to post the form to the folder,
and were done. Exchange Servers public folder
replication mechanism does the rest. At a pre-configured
time it sends a replication message to all the other servers
that have replicas of the candidate status folder. Those
servers will then maintain a current copy of this item
in their replica of the folder, which will be available
to users in that site.
Reviewing this process, the recruiter in an outlying
district either adds or modifies a candidate in his or
her local Access database. He or she then clicks on the
Update HQ button, which, by using ActiveX
automation, posts a custom form to the local Exchange
servers Candidate Status public folder. Later, using
the existing Exchange organization infrastructure, the
form is replicated to the hub site (and other sites) where
HR can look up the status of a given candidate from a
copy of the form on the local (hub) Exchange Server.
A Look at the Form
We created the custom form shown in Figure 6 using Outlook
98, which has a forms designer. This form is based on
the post form, thus allowing for conversation threads
on a given candidate. Notice that we dont post all
the information for a given candidatejust that needed
by HR. The Outlook forms designer allows us to present
the information stored in the form item in two ways: one
for when a user is composing the form and the other for
when a user is reading the form. In this application the
differences between the two views are negligible.
|Figure 6. The custom form allows
for “conversation threads” on a given candidate among
the users of the application..
The form also contains user-defined properties for storing
the custom properties of each candidate. The Outlook forms
designer provides a Field Chooser (shown in Figure 7)
to explore all the various kinds of properties that a
given forms item can contain. We used this to create our
custom user propertiesbetter known as fields.
|Figure 7. The Field Chooser gives
you an easy way to find out what properties a given
item on a form contains.
Next, we created controls on the form using a familiar
version of the Visual Basic toolbox, by dragging and dropping
the controls onto the form page. The last step was to
tie the control to a given user property or field. Right-clicking
on the control brings up the Properties dialog box. Selecting
the Value tab, we can than choose which field to bind
the control to. There are many additional properties you
can set, like validation, dropdown lists, and default
values. Finally, we published the form to the folder,
where it was automatically replicated to all sites containing
a replica of the folder.
The last two tasks we did to complete the application
was to create a custom view that allowed HR users to find
a given candidate quickly and to set up the security on
the folder. We created a custom view on the folder based
on the standard e-mail table view, which had columns corresponding
to the new fields we had created in the form, sorting
on the column. This allowed users to track down any candidate
information they wanted by sorting on any of the views
columns. The custom viewlike the formwas replicated
to all sites through Exchange.
The Access 97 database has a security database separate
from the Exchange security database. We didnt try
to integrate these two because the information in this
application isnt highly sensitive, it would have
been difficult to do, and security took place at other
places in the overall system. All users must have an Exchange
account, which also means they must have a Windows NT
account. We then set up security at the folder level for
each Exchange user. All recruiters need read, create,
edit, and delete permissions to post candidate information.
While HR users only needed read permission, we gave them
full permission in case they wanted to add notes on the
candidate. Permissions are configured by right-clicking
on the folder in the Outlook client and selecting Properties
A Real Problem
The best solutions solve real problems. We needed to
share information across the enterprise while maintaining
a robust local application to track recruitment data.
We leveraged the RAD and relational capabilities of Access
97, the fault-tolerant, reliable replication capabilities
of Exchange Server, and the fast 32-bit forms and views
of Outlook 98. Exchange Server handled application data,
forms, and view distribution, turning a desktop program
into an enterprise application. Voilà! The combination
makes an awesome enterprise-wide recruiting system.