Building ChemFinder in VB
Or: An Exercise in Visual Basic, MolServer, and CDAX
14 Jun 02
There are various ways to manage chemical data from Visual Basic. One is to call the ChemFinder executable via Automation. This gives you access to all ChemFinder objects, plus ChemFinder Automation Language, so you can not only manage the database but also execute menu commands and form-building operations. However, for some projects this approach may not be suitable. It restricts you to a specific database arrangement, and invokes a lot of UI code you may not need. For more information, see the ChemFinder SDK site.
A more direct channel to chemical data is MolServer, the chemical COM component. MolServer can access structures in ChemFinder databases, SDFiles, collections of CDX files, Microsoft Office documents, and other sources; it can search them, update, delete, build databases, and export to other formats. It is lightweight, with a fairly simple interface. Public documentation is evolving at the MolServer SDK site.
In this project, we build a little VB app which has many of the capabilities of ChemFinder, but uses only MolServer. It is built with MolServer 7 and other components of ChemOffice 2002. A future version will demonstrate MolServer 8 and CDAX 8.
Click to download VB project
The application is fairly unimaginative. It goes by the unimaginative name of VBApp, and has an unimaginative interface with a little display form and a panel of buttons:
VBApp Main Form
Form elements and buttons are as follows:
Structure box: ChemDraw ActiveX control for display and in-place editing. The toolbar shows up when you click on the structure box.
Mol_ID, Molname boxes: standard text boxes bound to the database via the Data Control.
Formula, Molweight boxes: standard text boxes managed by the app, bound to the structure.
Data Control: standard VB control connecting to the relational database and providing browse tools.
List size: text managed by the app.
Open: brings up MolServer Open Dialog to select a new data source.
Export To: opens a MolServer for output, then copies current list of structures to it.
Enter Query: blanks form for query input, begins query mode until ESC or Find.
Find: execute search for query on display. If not in query mode, Find searches for current structure.
Retrieve All: restore to full list after search.
Add Record: brings up separate form for input of new structure (shown below).
Delete Record: deletes current record.
Debug: for internal use.
The database setup for this application is the standard ChemFinder arrangement: relational data is stored in an mdb file (or other ADO-accessible database), with structures stored separately in mst/msi files (or other MolServer source), and a column of numeric mol id's relating the two. Relational data is managed by standard VB database mechanisms, structures managed by MolServer, and the two are kept in synch by the VB code. A database like this can be accessed both from VB and from ChemFinder.
You must have ChemOffice 7 installed. This provides MolServer 7 and the ChemDraw 7 control.
Download and unzip the project into a folder. To download, click the link above or here.
Copy ChemOffice\ChemFinder\Samples\cs_demo.* into the same folder. The data control is hard-wired to look for cs_demo if a database is not otherwise specified, so having it available will prevent a startup glitch.
Click Open and browse to the ChemFinder database (.cfw file) of your choice. The file is processed by a command-line utility called formhdr.exe (included in the project folder), which extracts db, table, and mst paths from the cfw file.
When you exit, the currently-open database info is saved to the Registry, and will be used the next time you run the program. Settings are found under
HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MolServerTestApp
Three keys are stored: DBPath, pathname of mdb file or ADO connect string; Table, name of the table containing mol id's; MstPath, pathname of mst file or MolServer connect string. You can edit these by hand if necessary.
How It Works
The application consists of two forms and three code modules, with the main code in MolForm. At startup (Form_Load), the program finds connection info in the Registry or uses default values. The database path is handed to the data control, and the mst path (or connect string) is used to open a MolServer Document object:
The data control automatically fills the data-bound boxes when necessary, e.g. on opening or moving to a new record. To fill the structure-related boxes, we watch for an event indicating that the Mol_ID has changed, then retrieve the associated molecule, formula, and molweight. Note that this technique has a bonus side-effect: if you type a new value into the Mol_ID box, it automatically brings up the related structure instead of editing the ID, which is not allowed anyway. The code boils down to:
To search, you begin by clicking Enter Query. All this does is clear the form and set a flag indicating we are in query mode (thus preventing any edits from being stored into the database):
When you click Find, the contents of the controls are transferred to a query object (a MolServer.SearchInfo), which is passed to the MolServer Search method. This prepares the query but doesn't actually do the search until you call Start. After that, you have the option of (a) repeatedly polling to see whether the search is complete, and posting intermediate results, or (b) calling WaitForCompletion so the search will run to completion before returning. We've used the latter method. The code for Find amounts to:
The result of the MolServer search is a hitlist of ID's. To convert this into a recordset, we generate from it an IN clause, and use that in a SELECT statement:
After this operation, you are browsing the hits only. You can iterate over them like this:
or reset to the full list using Retrieve All, which requeries the full table:
When you edit data in a bound control, then move to a different record, the data control automatically stores the update. The application hooks into this process by trapping the Validate event, when it can abort if the data is invalid. In our case, we're using this event as an opportunity to store the structure if it has been changed (as indicated by a flag set by the DataChanged event from the ChemDraw control). The code:
Adding a new record is a little different. To do this cleanly and allow it to be cancelled, we've set up a separate form as a sort of modal dialog:
Add Record: fill form and commit
When you click New Record, this form loads and calls the MolServer method AssignID, which reserves the next available ID in the structure source. You then draw the structure, add data (in this case just Molname), click Commit, and the data is moved to the parent form and committed from there:
The Export function is not quite as useful as it will become. In principle it allows a list of structures to be sent to any type of destination; in practice it is limited since most MolServer 7 sources are read-only. We will improve this in MolServer 8. Meanwhile, the current function can be used to build a ChemFinder database. The code is as follows, beginning with an Open dialog for selecting a destination, then a series of GetMol from the source and PutMol to the destination:
The best way to use this function is to create an empty database and form using ChemFinder, then select it as the output destination. Each Export operation appends the current list to the database.