Creating Windows applications from reusable components is the first step toward a truly object-oriented development environment. Building database applications from a collection of standardized objects can save a substantial amount of programming and
testing time. Another advantage of the component approach to application design is that you can structure your object architecture to model business or other organizational practices. In database applications, the major role of reusable components is to
implement business rules. The basic definition of a business rule is a set of constraints that applies to the data entry elements of a specific business activity, such as entering orders or issuing invoices.
One of Visual Basic 4.0's most important new features is the ability to create OLE Automation servers. You can use Visual Basic's 4.0 OLE Automation capabilities to encapsulate code that is common to multiple applications in a single object and then
access the object from any client application that supports OLE Automation. Thus, an OLE Automation server you create with Visual Basic 4.0 can be used not only with Visual Basic applications, but also by Microsoft Excel 5+, Access 2+, Project 4+, Internet
Explorer 3.0, and Internet Information Server. This chapter describes how to create 32-bit database-related local OLE Automation servers that reside on the same computer as the client application. Chapter 23, "Creating and Managing Remote Automation
Objects," is devoted to shared OLE Automation servers that reside on a remote computer.
The traditional structure of a Visual Basic application consists of a collection of forms with embedded event-handling subprocedures. Modules incorporate declarations of Public variables, plus independent subprocedures and functions that are
called by one or more event subprocedures or, in the case of Sub Main, by instantiation of the application. If you apply modular application design methodology to the initial design of your programs, you can take advantage of the reuse of standard
forms and modules in multiple applications. Substituting Visual Basic 4.0 hierarchical class modules for conventional module code is a first step toward creating reusable objects that don't require forms. The primary limitation of Visual Basic forms and
class modules is that they only can be used as elements of a Visual Basic application.
Creating a standalone OLE Automation server to contain common forms and related code simplifies project management by providing an independent object that provides a predefined service or set of services. Once you create an OLE Automation server with
Visual Basic 4.0, you can add a reference to the server in any client application that supports VBA. Once you've added the server reference, you can use the client application's Object Browser to examine the Public properties and methods exposed by
the server. Your client also can read and write the values of Private properties with Property Get and Property Let procedures, respectively.
In database applications, OLE Automation servers often are used to isolate data services and user services. Data services are provided by a RDBMS, such as the Jet database engine or Microsoft SQL Server. In the case of SQL Server, data
services are implemented by ODBC in conjunction with Jet's Data Access Object (DAO) or the Remote Data Object (RDO). User services represent the visual interface to the data incorporated in a client application. Interposing an OLE Server (business
services) between data services and user services results in a three-tiered structure, as shown in Figure 16.1. The isolation of data-specific elements, such as database, table, and field names and data types, from the user services layer makes the
applications that provide user services independent of the underlying data structures.
Figure 16.1. Contrasting two-tier and three-tier database application structures.
There are a variety of naming conventions for each of the three tiers of the database application architecture shown in Figure 16.1. This book's use of the term data services includes functions performed by the RDBMS implemented by stored procedures and triggers, or Jet's built-in data validation and referential integrity maintenance features for .mdb files. User services represent the visual front-end to the RDBMS. In migrating from a conventional two-tier architecture to a three-tier system, much of the code that would ordinarily be included in user services client, such as logging in to the database, is moved to the business services layer. Three-tier database application design usually implies a client/server RDBMS back-end; this book does not distinguish between client/server and Jet databases when discussing the partitioning of database applications. In three-tier architecture, all client applications use the business services layer to gain access to the underlying data services, which may be provided by more than one RDBMS.
Another advantage of using an OLE Automation server is a built-in version control system for Visual Basic clients. This rudimentary version control system is implemented by establishing a version number for the server's type library. Type library
version numbers are not the same as the file version number you specify in Visual Basic 4.0's EXE Options dialog. The Visual Basic client's .exe file includes the type library version number for each OLE Automation server for which a reference is included
in your project. If the type library version number of your OLE Automation server doesn't correspond to the embedded version number, the client displays error 429, OLE Automation server cannot create object, when the server is instantiated by a client's
reference to one of its methods or properties. You implement version control by maintaining a reference to the prior version by entering the path to the compiled prior version in the Compatible OLE Server text box of the Project page of the Options dialog.
Visual Basic defines the following three degrees of compatibility:
Visual Basic 4.0 lets you create either in-process servers (OLE DLLs) that run in the same process space as the client application or out-of-process (.exe) servers that run in their own process space. Neither type of server exposes events, although the
asynchronous callback feature of OLE Automation servers can emulate the firing of an event. Visual Basic 4.0 remains an interpreted language, so you must include VB40016.DLL (914KB) or Vb40032.dll (731KB) with your server DLL or executable that is used by
a VBA-enabled application other than Visual Basic.
With the exception of a common login dialog for all database user services, business services implemented by OLE Automation servers seldom include forms or other visual elements, such as message boxes. This is especially true for local out-of-process servers that are candidates for conversion to the Enterprise Edition's Remote Automation Objects (RAOs). A RAO would display its visual elements on the server, not the client. Therefore, any OLE Automation server should include complete runtime error trapping and error reporting code. Generic error handlers for the client can be implemented as in-process OLE DLLs.
The following two sections describe the advantages and limitations of in-process and out-of-process servers for three-tier database applications.
In-process OLE Automation servers are analogous to conventional Windows DLLs. Instead of exporting functions to client applications, in-process servers expose one or more objects created by class modules, plus the properties and methods applicable to
each object. OLE controls are an extension of the in-process OLE Automation server concept. OLE controls expose to the client only a single object, but also expose events associated with the OLE control's user interface. It's useful to think of an
in-process server as an invisible OLE control.
Following is a list of the salient features offered by in-process servers:
If your primary goal is to maximize performance, in-process servers are the logical choice for 32-bit clients. Executing in-process server code is almost as fast as executing the same code contained in a conventional Visual Basic 4.0 form or module.
In-process servers that require visual elements and are intended for production applications are candidates for conversion to OLE controls or the lighter-weight ActiveX controls introduced by Microsoft in Spring 1996. You can use an in-process server to emulate an OLE control.
Out-of-process OLE Automation servers, compiled as .exe files, offer greater versatility at the expense of somewhat slower operation. Out-of-process servers offer the following features:
In most cases, you first compile and test new servers as in-process .exe files. You then can compile a second version as in in-process .dll, which allows you to compare the performance of the two versions.
One of the most common development scenarios for OLE Automation servers is extracting common code and, in many cases, form(s) from existing Visual Basic 3.0 or 4.0 applications and converting these Visual Basic objects to in-process or out-of-process
OLE Automation servers. Converting existing code to complex OLE Automation servers also is more instructive than creating servers to perform trivial functions, such as database login dialogs.
The example described in the following sections is derived from components of a production Visual Basic 3.0 database application called CycleTime. CycleTime is a client/server front-end application designed for reporting semiconductor sales and
production metrics of a variety of products and product groups over a wide range of time periods. The metrics, in this case, are the elapsed times in days and fractions of days between the occurrence of two specific events, such as testing and packaging.
The 16-bit production version of CycleTime has 24 forms and six modules. Several of the forms use hidden controls in order to serve multiple purposes. Two of the CycleTime forms, PER_SEL.FRM for date manipulation and PROD_GRP.FRM for product grouping,
together with their module files, are particularly useful for creating a variety of related 32-bit Visual Basic, Access, and Excel applications for analyzing sales and production metrics, as well as for financial reporting. The sections that follow
describe the process used to convert the original Visual Basic 3.0 PER_SEL.FRM and PER_SEL.BAS components to an OLE Automation server. Conversion of PROD_GRP.FRM and PROD_GRP.BAS, which includes database operations, to a three-tier OLE Automation server is
described later in this chapter.
The process for upgrading components of a 16-bit Visual Basic 3.0 application to 32-bit Visual Basic 4.0 in preparation for conversion of the components to an OLE Automation is similar to that involved in upgrading complete applications from 16 to 32
bits. Assuming that you have 32-bit OLE controls to replace all of the .VBXs in the forms you plan to convert, follow these steps:
The preceding steps were used to convert the components of 16-bit CycleTime (CYCLTIME.MAK) to the 32-bit version (CycTim32.vbp), which includes both the Period Selection and Product Grouping functions, plus a simple form to display the Period Selection
and Product Grouping forms. Figure 16.2 shows the Period Selection form run from the demonstration form. Open and run CycTim32.vbp, then click the Period Selection button to display the Period Selection form. You select from various date formats with the
option buttons and choose a time period with one of the five enabled option buttons. (Quarterly periods are not implemented in this version.) You can choose arbitrary starting and ending dates with the spin buttons in the From Date and To Date frames. When
you click the OK button of the Period Selection form, values of the global gvarDateFrom, gvarDateTo, and gstrDateRange variables appear in the text boxes of StdDemo.frm. The output of Per_Sel.frm is used to create a WHERE Date BETWEEN 'From
Date' AND 'To Date' constraint for queries against an SQL database. Forms of this type are common in applications created for large organizations that use a variety of date formats and calendars.
Figure 16.2. Running the Period Selection form from the StdDemo form.
The source code files for the CycTim32.vbp project and the other example applications of this chapter are located in the \DDG_VB4\32_bit\Chaptr16 folder of the accompanying CD-ROM. The code for the Period Selection form (Per_Sel.frm and Per_Sel.bas) includes a complex series of calendar algorithms written by the firm that uses the application. The calendar algorithms, which are not optimized, use the Variant data type exclusively and don't employ the Leszynski variable naming conventions used elsewhere in this book. "Wrapper" code added to adapt the calendar algorithms uses Leszynski naming conventions.
OLE Automation servers consist of at least one class module (.cls), plus other optional modules and forms. The class module is necessary to cause Visual Basic 4.0 to create a type library for the server. (To add a class module to a project, choose
Insert | Class Module.) The following sections describe naming conventions for servers, adding a Sub Main procedure to a code module, adding a typical class module for a single-purpose server, testing, and debugging the server.
The Creating OLE Servers manual that accompanies the Professional and Enterprise editions of Visual Basic 4.0 provides the foundation you need to write new, general-purpose OLE servers. This chapter is intended to supplement, not replace, the Creating OLE Servers manual.
Visual Basic automatically creates an entry for the server in the Windows 95 or Windows NT Registry, or in the REG.DAT file of Windows 3.1+ when you run or compile a Visual Basic 4.0 OLE Automation server. Before you create an OLE Automation server,
you should define the names that the server registers. The most important names are those of the type library for the server and for the classes (objects) exposed by the server, as described in the following list:
In addition to specifying the class name, you also must set the type of instantiation allowed for your class module in the Instancing text box of the class module's Properties sheet. Following are the three types of server instancing, in descending
order of their most common usage:
Finally, you must set to True the Public property of the class module that contains the methods and/or properties you want to export to other applications. Public class modules provide the interface to the object(s) of the class(es) defined by
your class module(s).
The preceding discussion may appear to be out of order. Ordinarily, you might write code and then set the project name and class name properties of an OLE Automation server when you first run the server. The importance of correctly setting the properties of the server is such that you should set these values before adding code to the class module.
All OLE Automation servers should have a Sub Main procedure, regardless of whether Sub Main includes any code. (OLE DLLs require a Sub Main procedure.) You can use the Sub Main procedure to test the value of the
App.StartMode property to determine if the server is started in standalone or OLE server mode, as in the following example:
Sub Main()
'The following code lets you use standalone mode
'with out-of-process servers
If App.StartMode = vbSModeStandalone Then
frmPeriodSelect.Show vbModal
End If
End Sub
Adding a test of the App.StartMode property lets you demonstrate out-of-process servers having user interface components without running a test application.
When creating an OLE Automation server from elements of an existing project, such as CycTim32.vbp, you add a class module to a project that exposes method(s) and, optionally, properties of objects of the class. Using arguments of methods to set and
return property values is faster than setting and reading the values of individual properties exposed by Property Let and Property Get procedures, respectively, for out-of-process servers. (Property procedures are useful, however, for access to property
values that you only set occasionally.)
If your OLE Automation server contains only a single exposed object, it's likely to require only one method, which you create with either a Public Sub or Public Function procedure. Using a Function procedure lets you return a value
that indicates application of the method succeeded (True), was canceled by the server (False), or encountered an execution error (Err.Number value, Long). If your OLE server includes a visual element, such as a modal form, a
Show method is the logical name for a function to display the dialog. You can pass Variant values to and from the arguments of the Show method. Listing 16.1 shows the code for the Show method of the PeriodServer.PeriodSelection class created by the
PerSel.cls module of the PerSel32.vbp project. The Show method returns three user-chosen values, DateFrom, DateTo, and DateRange, to the client application.
Listing 16.1. Code to implement the Show method of the PeriodServer.PeriodSelection class.
Option Explicit
Public Function Show(DateFrom As Variant, _
DateTo As Variant, _
DateRange As Variant) As Long
'Declare the class instance
Dim frmPerSel As New frmPeriodSelect
Show = True
'Show the frmPerSel form modally
frmPerSel.Show vbModal
'fCancel is set True by the cmdCancel_Click procedure
'fCancel also can be set by error-handling routines
If fCancel Then
Show = False
Else
'Assign values to the function arguments
DateFrom = gvarDateFrom
DateTo = gvarDateTo
DateRange = CVar(gstrDateRange)
End If
'Unload the modal form
Unload frmPerSel
End Function
The source code for the PerSel32.vbp project, PerSel.frm, PerSel.cls, and PerSel.mod, appears in the \DDG_VB4\32_bit\Chaptr16 folder of the accompanying CD-ROM. You can run PerSel32.exe as a standalone application. The code of PerSel.frm and PerSel.mod differs from that of the original source files, Per_Sel.frm and Per_Sel.bas, because of issues discussed in the "Debugging the Period Selection Server" section, later in this chapter.
Once you've added the class module(s) needed by your server, verify that the property settings for your project and the class module are in accordance with those specified in the preceding "Specifying the Properties of OLE Automation Servers"
section. Choose Run | Start with Full Compile to perform a preliminary test of your server code.
If your server runs and then shuts down after closing a modal form, dialog, or message box, it's likely that you neglected to select the OLE Server option button in the StartMode frame of the Option dialog's Project page.
You should create an independent Visual Basic 4.0 project to test your server before you compile the server to a .exe or .dll file. The test project needs only to include a single form with a command button to activate the OLE Automation server with
Dim objName As New ClassName and typRetValue = objName.MethodName[(Arguments)] statements. Text boxes are useful to display return values of arguments; alternatively, you can use the
Debug.Print ArgName statement to check argument values in the debug window. Listing 16.2 shows the code required to create an instance of the PeriodSelect class and display the return values of the arguments of the Show method in three text
boxes.
Listing 16.2. VBA code to instantiate a member of the PeriodSelection class and test the Period Selection server.
Option Explicit
Private Sub cmdTestPerSel_Click()
'Test procedure for PeriodSelection class
Dim fSuccess As Long
Dim varDateFrom As Variant
Dim varDateTo As Variant
Dim varDateRange As Variant
'Full object syntax:
Dim svrPerSel As New PeriodServer.PeriodSelection
'Alternative shorthand object syntax if class
'name is not duplicated in Registry:
'Dim svrPerSel As New PeriodSelection
'Apply the Show method
fSuccess = svrPerSel.Show(varDateFrom, _
varDateTo, _
varDateRange)
If fSuccess Then
'Populate the text boxes with return values
txtDateFrom.Text = Format(varDateFrom, "mmm-dd-yy")
txtDateTo.Text = Format(varDateTo, "mmm-dd-yy")
txtDateRange.Text = varDateRange
Else
'User canceled or error occurred
txtDateFrom.Text = "Error"
txtDateTo.Text = "Error"
txtDateRange.Text = "Error"
End If
End Sub
To test the new server, follow these generic steps:
Figure 16.6 shows the Test_PS.frm of the Test_PS.vbp project with the Period Selection form open after previously launching and closing the Period Selection server. Follow the preceding steps to run PerSel32.vbp and Test_PS.vbp to demonstrate the
server test, then compile PerSel32.vbp and test the final version. Figure 16.7 shows a similar test form created with Access 95. Listing 16.3 shows the code behind the frmPeriodSelect test form in the CyclTime.mdb database.
Figure 16.6. Testing the PerSel32.vbp Period Selection server with the Test_PS.vbp project.
Figure 16.7. Testing the PerSel32.vbp Period Selection server with an Access 95 test form.
Listing 16.3. VBA code to instantiate a member of the PeriodSelection class and test the Period Selection server.
Private Sub cmdTestPerSel_Click()
'Test procedure for PeriodSelection class
Dim fSuccess As Long
Dim varDateFrom As Variant
Dim varDateTo As Variant
Dim varDateRange As Variant
'Full object syntax:
Dim svrPerSel As New PeriodServer.PeriodSelection
fSuccess = svrPerSel.Show(varDateFrom, _
varDateTo, _
varDateRange)
If fSuccess Then
'Note that Access requires the SetFocus method
txtDateFrom.SetFocus
txtDateFrom.Text = Format(varDateFrom, "mmm-dd-yy")
txtDateTo.SetFocus
txtDateTo.Text = Format(varDateTo, "mmm-dd-yy")
txtDateRange.SetFocus
txtDateRange.Text = varDateRange
Else
'User canceled or error occurred
txtDateFrom.SetFocus
txtDateFrom.Text = "Error"
txtDateTo.SetFocus
txtDateTo.Text = "Error"
txtDateRange.SetFocus
txtDateRange.Text = "Error"
End If
End Sub
Server applications that compile without errors may not behave as expected. As an example, code in modules that refer to specific forms by their Name property is virtually guaranteed to fail. The reason for this problem is the instancing of form
objects by the Dim frmInstanceName As New frmOriginalName statement. Code that explicitly refers to frmOriginalName does not execute correctly (or at all) because the active instance of the form is frmInstanceName,
not frmOriginalName. Using code in PerSel.bas imported without modification from the original Visual Basic 3.0 PER_SEL.BAS module results in failure to update label and 3-D panel captions in several locations of the 32-bit Visual Basic 4.0
PerSel.frm form.
The most expedient remedy is to move procedures that contain explicit references to the instanced form from the .bas module to the .frm module, then remove the frmOriginalName prefixes of references to control properties. Listing 16.4 shows the
"before" and "after" versions of the SetCalDates subprocedure. The first version of the SetCalDates subprocedure was contained in PER_SEL.BAS. The second version of SetCalDates is the revised version relocated to PerSel.frm. Similar
treatment is applied to the GetPeriodToDate and GetDates subprocedures. Moving the procedures to the form module is feasible because the F_DATE and T_DATE variables in the subprocedures are declared Public in PerSel.bas.
Listing 16.4. The original SetCalDates subprocedure from PER_SEL.BAS and the revised version of SetCalDates moved to PerSel.frm.
Sub SetCalDates()
'Purpose: Use date format functions to display dates (faster)
'Note: Moved to frmPeriodSelect and "frmPeriodSelect." removed
frmPeriodSelect.lblDateFrom(1).Caption = Format(F_Date, "mmm")
frmPeriodSelect.lblDateFrom(2).Caption = Format(F_Date, "dd")
frmPeriodSelect.lblDateFrom(3).Caption = Format(F_Date, "yy")
frmPeriodSelect.tdpDateFrom.Caption = _
Format(F_Date, "dddd, mmm d, yyyy")
frmPeriodSelect.lblDateTo(1).Caption = Format(T_Date, "mmm")
frmPeriodSelect.lblDateTo(2).Caption = Format(T_Date, "dd")
frmPeriodSelect.lblDateTo(3).Caption = Format(T_Date, "yy")
frmPeriodSelect.tdpDateTo.Caption = _
Format(T_Date, "dddd, mmm d, yyyy")
frmPeriodSelect.Date_Label = "From " & _
Format(F_Date, "mmm-dd-yy") & " to " & _
Format(T_Date, "mmm-dd-yy") & " (" & _
(DateDiff("d", F_Date, T_Date) + 1) & " days)"
End Sub
Sub SetCalDates()
'Purpose: Use date format functions to display dates (faster)
'Note: Moved from modPeriodSelect and "frmPeriodSelect." removed
lblDateFrom(1).Caption = Format(F_Date, "mmm")
lblDateFrom(2).Caption = Format(F_Date, "dd")
lblDateFrom(3).Caption = Format(F_Date, "yy")
tdpDateFrom.Caption = Format(F_Date, "dddd, mmm d, yyyy")
lblDateTo(1).Caption = Format(T_Date, "mmm")
lblDateTo(2).Caption = Format(T_Date, "dd")
lblDateTo(3).Caption = Format(T_Date, "yy")
tdpDateTo.Caption = Format(T_Date, "dddd, mmm d, yyyy")
Date_Label = "From " & Format(F_Date, "mmm-dd-yy") & _
" to " & Format(T_Date, "mmm-dd-yy") & _
" (" & (DateDiff("d", F_Date, T_Date) + 1) & " days)"
End Sub
Declaring Public variables used by multiple instances of a class in an OLE Automation server is not considered a "generally-accepted automation server programming practice" (GAASPP). The problem with Public variables is that
instances of other classes created by multiple client applications that refer to the Public variables may obtain values inappropriate to a particular class instance. If your server is Creatable SingleUse, the Public variable problem doesn't
occur. Similarly, a Creatable MultiUse server having a single object doesn't suffer from Public variable problems because OLE 2+ serializes the application of methods to successive instances of classes. Diligent testing with multiple client
applications (Access 95 and Visual Basic 4.0) of the Creatable MultiUse PerSel32.exe server created from PerSel32.vbp does not reveal a problem with the use of Public date variables.
The code for the PerSel32.vbp project doesn't include error trapping, other than generating an internal error message if the user attempts to set an invalid date with the spin button controls of PerSel.frm. Extensive testing and large-scale commercial
deployment of the CycleTime front-end indicates that error trapping is not necessary for the relatively simple event-handling code and the date manipulation algorithms of the original source code.
If error handling is necessary, On Error GoTo ProcNameError statements can be added to suspect procedures. The error-handling code can pass the value of Err.Number to a generic error-processing procedure in PerSel.frm that returns
the Long value of Err.Number to the Show method's return value prior to execution of an Unload frmPerSel statement.
OLE Automation servers that require multiple forms present a more formidable programming challenge than invisible servers or servers that display only a single modal form. The PrdGrp32.vbp project is an example of an OLE Automation server that requires
several forms and message boxes to provide the equivalent functionality of the original Product Grouping components. PrdGrp32.vbp qualifies for three-tier status because it employs the Jet 3.0 database engine as a lower-level OLE Automation server.
Three-tier architecture purists would argue that applications employing PrdGrp32.exe don't qualify for three-tier status because PrdGrp32.exe cannot be run remotely on a networked application server. (As noted earlier in this chapter, Remote Automation servers created with Visual Basic 4.0 cannot incorporate user-interface components.)
PrdGrp32.vbp demonstrates a alternative to PerSel32.vbp's technique, described in the preceding sections, for dealing with explicit references to form names. PrdGrp32.vbp includes three forms, ProdGrp.frm, LoadGrp.frm, and SaveSel.frm. LoadGrp.frm, a
simple message form that advises users to wait while the ProdGrp.frm opens, cannot be modal; thus, LoadGrp.frm only appears when running PrdGrp32.exe in standalone mode. (In standalone mode, ProdGrp.frm is opened as a modeless form.) SaveSel.frm, which
lets users name custom product grouping selections for saving in their local CyclTime.mdb database, must reference a variety of controls on ProdGrp.frm.
The sections that follow describe the overall design of the CycleTime Product Grouping server and the changes to the original code that are necessary to transform these elements into an OLE Automation server.
The Product Grouping server provides users with a means to specify multiple categories of semiconductor products for collective analysis. Products are classified in a six-level hierarchy, the lowest (leaf) level of which is called AFM. AFMs use an
arbitrary two character alphanumeric code to specify a group of closely related products. The overall design of the Product Grouping server is applicable to a wide variety of database front-ends that deal with hierarchical list selections.
The output of the Product Grouping server creates a SQL IN clause that consists of the field name followed by IN(, a comma-separated list of leaf-level AFMs enclosed in single quotes, and closing parenthesis. The IN clause text can be used as a
criterion for a SQL statement created by any application capable of querying the server RDBMS. Following is an example of the IN criterion clause for the All Corporate (Test) grouping selection:
i_afm_c IN('M9', '0F', '17', '1H', '25', '2A', '2G',
'2X', '3N', '6X', '95', '99', '9A', '9D', '9N', '9X',
'E1', 'M1', 'TR', '10', '11', '3A', '3B', '3C', '7A')
Standard and user-specified grouping selections are stored in the tblGroupSelections table of the user's local CyclTime.mdb database. Each record includes the selection name (SelName), hierarchy structure name (StructName), item data to populate the
list box (SelData), a list of selected AFMs (SelAFMs), a list of leaf-level AFMs corresponding to the selected AFMs (LeafAFMs), and the text of the SQL IN clause (LeafSQLIn). Items in the SelData, SelAFMs, and LeafAFMs fields are separated with backslashes
(\). The FreqQueryTime and DetailQueryTime fields store the time in seconds for execution of the user's most recent summary and detail query against the client/server RDBMS.
CyclTime.mdb and the source code for the PrdGrp32.vbp project are included in the \DDG_VB4\32_bit\Chaptr16 folder of the accompanying CD-ROM. You can run PrdGrp32.exe as a standalone application. Alternatively, you can open and run PrdGrp32.vbp, then open and run Test_PG.vbp in a separate instance of Visual Basic 4.0 to test PrdGrp32.vbp as an OLE Automation server. Make sure that the reference to the CycleTime Product Grouping Server in Test_PG.vbp points to the location of PrdGrp32.vbp on your computer.
Upon opening PrdGrp.frm, the left-hand Outline control (Available product groupings) displays a collapsed version of the entire product hierarchy of the firm. The items in the Outline control are derived from one or more lists of product structures
contained in table(s) that periodically are updated by downloads from the server RDBMS. The tblNSORG0 table contains the data for the standard product structure in a non-relational format containing repeating rows. This table is generated by a COBOL
program from the firm's mainframe database as a flat (ASCII) file. The flat file is imported into Excel for review and then imported into a Jet table, which is subsequently exported to the server for distribution to users. The LoadAvailList subprocedure of
frmProdGroup adds the items from the records of tblNSORG0 into the appropriate hierarchical level of the Outline control. (Most of the code included in the LoadAvailList subprocedure is based on a routine developed by an employee of the semiconductor firm
and does not use Leszynski variable naming conventions.)
Double-clicking a folder in the Outline control expands or collapses display of the list hierarchy. Clicking the Level 1. . .Level 5 command buttons in the Available list shows an increasingly detailed view of the product hierarchy. Clicking the AFM
button expands the list to display the entire product hierarchy. Right-clicking an item in the Available list (or selecting an item and clicking the Add -> button) copies the item(s) from the Available to the Selected Outline control. Selecting an item
in the Selected list and clicking the Delete button removes the item and any subordinate items. Changes to the Selected list mark the grouping "dirty." The user saves the revised product grouping by clicking the Save Selection button or clicking
the OK button. In either case, the Save Current Selection form (SaveSel.frm) appears, as shown in Figure 16.8. SaveSel.frm contains all of the code necessary to update an existing grouping record and to create a new record in tblGroupSelections.
Figure 16.8. The SaveSel.frm for saving a new or modified product grouping to the tblGropuSelections table of CyclTime.mdb.
The original code of SAVE_SEL.FRM contains many references to frmProdGroup, the main form of the component. To minimize the amount of code re-writing and testing time in the conversion of the product grouping function to an OLE Automation server, the
instance of the form class is made accessible to all form and module code by adding a Public gfrmGrouping As New frmProdGroup statement in the declarations section of the PrdGrp.bas module, instead of using the conventional Dim
frmGrouping As New frmProdGroup statement in the class module. Declaring the main form variable as Public allows replacement of SaveSel.frm's frmProdGroup references with gfrmGrouping. Declaring as Public object variables
referenced by class modules isn't recommended as a standard practice when creating new OLE Automation servers. The ability to use this technique, however, makes conversion to OLE Automation servers of existing, complex Visual Basic application components a
relatively easy process.
All of the data created or updated by an instance of frmProdGroup is contained in the current record of the tblGroupSelections of CyclTime.mdb, so it's not necessary to provide arguments for the Show method or to expose other properties of the
ProductGrouping class. This simplifies the code for the function in PrdGrp.cls to create the Show method, which appears as follows:
Public Function Show() As Long gfOK = False 'Show frmProdGroup modally gfrmGrouping.Show vbModal 'Set True by OK button Show = gfOK Unload gfrmGrouping End Function
Tests that compare the speed of launching the Product Grouping form in its original implementation (from the CycTim32.vbp project) and the Creatable SingleUse version (from Test_PG.vbp) show that it takes about 10 percent longer to open the first
instance of the server version. (Running on a 16MB 8086DX4-100 PC, the opening time is about 27 seconds for the original version and 30 seconds for the server version.) Although performance is one of the major criteria in database application design, the
benefits of using component architecture in building complex database applications usually outweigh their somewhat slower response time.
This chapter described the types of 32-bit OLE Automation servers you can create with Visual Basic 4.0. One of the major tasks facing Visual Basic database developers is converting common elements of existing applications to in-process or
out-of-process servers. The examples in this chapter demonstrated how to take the common parts of an existing Visual Basic 3.0 database application and create a out-of-process server that you can use with any 32-bit development platform that supports OLE
Automation. Implementation of application components as OLE servers causes a perceptible performance hit, but the inevitable hardware upgrades accompanying transition to 32-bit applications are likely to mask minor server performance problems.
Remote Automation servers offer the prospect of implementing three-tier database applications that use out-of-process OLE Automation servers running on their own application server. Chapter 23 describes how to use the Enterprise Edition of Visual Basic
4.0 to develop and manage Remote OLE Automation servers.
