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
• 01/01/1999

A well-known axiom says that engineers create solutions to engineering problems. However, often I’ve 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 engineer’s 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. We’re a computer consulting firm with five U.S. locations. We wanted an automated business process for tracking engineering and programming candidates. We’re 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 more efficient.

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 candidates—like the status of applications—with 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 97’s replication functionality; but replication required manual intervention as well as a high bandwidth connection between the locations, which currently doesn’t 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 Infrastructure

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 aren’t included in the replication; and the public folder we’re replicating is called Candidate Status.

Figure 2. Replication for an Exchange public folder gets defined at the Candidate Status properties Replicas tab.

The Application

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 candidate’s skill set. Outlook functions enable the recruiters to send e-mail messages to candidates, make appointments in the recruiter’s Outlook calendar for interviews, and post candidate status information to the candidate status public folder. It’s this last function that we’ll focus on—it’s what makes this stand-alone program into an enterprise application.

Figure 4. The interface for the Access application.

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.)

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 we’ve 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.)

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. There’s 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, we’ve made a rule that there’s 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 folder’s 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 we’ve stated. In this case, it searches a user-defined field that contains the candidate name. A potential error could occur if we changed a candidate’s 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 can’t create true unique keys. If an item isn’t found for the candidate we’re 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 we’ve 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 form’s body to store a string with the skill set of the candidate.

All that’s left is to post the form to the folder, and we’re done. Exchange Server’s 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 server’s 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 don’t post all the information for a given candidate—just 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 properties—better 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 view’s columns. The custom view—like the form—was replicated to all sites through Exchange.

About Security

The Access 97 database has a security database separate from the Exchange security database. We didn’t try to integrate these two because the information in this application isn’t 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 | Permissions.

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.