Drill-down applications that employ graphs require a variety of methods for choosing the detail data that your application displays in the form of a grid or spreadsheet. This chapter extends your use of the Data Access Object to include unbound Grid
controls that are populated with data from Snapshot-type Recordset objects generated by SQL statements you create with Visual Basic code. The content of the SQL statement that serves as the argument of the OpenRecordset method is determined by the graph
that is currently displayed plus the graphs that were chosen at higher levels in the drill-down hierarchy.
The first sample application in this chapter shows you how to use the DoubleClick event in conjunction with the MouseDown event to establish an SQL WHERE clause criteria for the Snapshot-type Recordset object. You compare the X and Y
mouse coordinates returned by the MouseDown event with the coordinates of the labels of an area chart to determine the month for which detail data is desired. The MouseDown_DoubleClick technique that is described can be used for drill-down navigation
purposes with any type of graph or chart.
Preceding chapters of this book introduced you to some of the third-party, data-aware custom controls that were available as commercial products at the time this book was written. This chapter describes features of the Graphics Server Extended Graph
custom control as a navigation tool for drill-down, decision-support applications.
Specifically, this chapter describes how to use the Hot property and the HotHit event of the Graphics Server Extended Graph custom control to create a hot pie chart that explodes the wedge you click or double-click. The wedge you choose establishes a
WHERE clause criterion to populate a grid. An example of Visual Basic code to duplicate the features of the Extended Graph control's hot pie chart also is included.
To take full advantage of the sample applications created in this chapter, you'll need a license to use the Graphics Server Extended Graph third-party custom control. The Graphics Server Extended Graph control is part of the Graphics Server Graphing
toolkit for Windows, available from Pinnacle Publishing, Inc., the North American distributor for Bits Per Second Ltd. Addresses and telephone numbers for suppliers of third-party Visual Basic 4.0 enhancements discussed in this chapter appear in Appendix
A, "Resources for Visual Basic Database Applications."
The Graph control supplied with Visual Basic 4.0 (Graph32.ocx) and the Extended Graph control (Graphx32.ocx) both use the same CLSID (Class ID) number. Both graphing controls also use the same Registry database entries. As a result, you cannot use both controls simultaneously on the same computer system (although you may have both .ocx libraries on your hard disk). Code written for the standard Graph control will not always work with the Extended Graph control, and vice-versa. All of the graphs in a single Visual Basic project must use the same version of the Graph controlstandard or Extended. You may, however, alternately install the Graph and Extended Graph controls so that you can develop different projects using different versions of the graphing controls.
If you need or want to be able to alternate between using the standard Graph control and the Extended Graph control, you may change the installed version of the Graph control. To change the installed version of the Graph control, load any Visual Basic project which does not contain Graph controls. Choose the Tools | Custom Controls menu command and check the list of available custom controls to see if the version you want is already installed. If you want to use the standard Graph control, look for the custom control named Pinnacle-BPS Graph Control. If you want to use the Extended Graph control (assuming you have purchased and installed it), look for the custom control named BPS Extended Graph Control. If the control you want is not listed, first unselect any graph controls that are selected, and then use the Browse command button to locate the appropriate .ocx libraryGraph32.ocx for the standard Graph control, and Graphx32.ocx for the Extended Graph control. Selecting the new .ocx library in the Browse window adds it to the list of available custom controls, replacing the previously attached version. Selecting the .ocx library automatically causes the appropriate Windows 95 Registry database entries to be made.
Graphic decision-support applications should give the user easy access to detail data underlying the information that appears on the current form. For example, you might want to display the pie chart for sales by product category (frmMDIPieChart) for
the month when the user double-clicks one of the labels of the total sales graph. The code presented in Chapter 11, "Printing Reports with Code and Crystal Reports," requires that the user display the grid in order to be able to show the pie
chart. Letting the user click a label eliminates the additional step required to display the grid. You could use command buttons with month abbreviations to choose a month. However, the 12 command buttons would occupy a substantial amount of display real
estate and would detract from the appearance of the form. Detecting the mouse position when DblClick events occur and taking action based on the mouse coordinates is the most appropriate method to activate drill-down code.
Figure 12.1 shows the Year-to-Date Sales graph with a small test form that you use to develop the code that detects the mouse position when the user double-clicks a graph. (The Year-to-Date Sales graph is used to show the test form because this graph
does not launch the pie chart when you double-click the graph.) Adding a test form that displays the mouse coordinates and the dimensions of the chart saves a great deal of time when you need to determine the values of constants that convert mouse
coordinates into a specific drill-down action. You need to use the relative location of the mouse, expressed as the ratio of mouse position to the dimensions of the form, because the user can resize the form at will.
Listing 12.1 shows the code required to detect a double-click on the label of a chart and return the number of the month when the user double-clicks a month name abbreviation. The chtMonthly_DblClick and chtMonthly_MouseDown subprocedures and the
intTestDblClickMonth function are included in the frmMDIGraph form (Mdi_GRF3.frm). The sngMouseX and sngMouseY variables that hold the mouse position when you double-click the chart are declared as Public in the Drill_dn.bas module. The
chtMonthly_MouseDown subprocedure detects the mouse position prior to triggering the DblClick event. The small Test Month Mouse Position (TestMnth.frm) form, shown in Figure 12.1, appears only if the fTest flag (Public) is set to True.
Figure 12.1. Using a test form to display mouse click coordinates.
The examples in this chapter are drawn from the drill-down applications, Drill_dn.vbp and Drill_cc.vbp, whose forms and modules are located in your \DDG_VB4\32_bit\Chaptr12 folder. Using Drill_cc.vbp (which loads Drill_cc.bas) requires that you have the BPS Extended Graph custom control. If you don't have these custom controls, run Drill_dn.vbp, which loads Drill_dn.bas. The drill-down application builds on the forms and code developed in the earlier chapters of the book. Most code required to add the drill-down features to the application is incorporated in the Drill_cc.bas or Drill_dn.bas module. The drill-down sample application uses the Graphs.mdb database located in the \DDG_VB4\32_bit\Chaptr10 folder as its data source.
Listing 12.1. Code to detect the double-clicking of a month label on a graph.
Private Sub chtMonthly_DblClick() 'Purpose: Display the grid with a double-click Dim intMonth As Integer 'Test vertical position of mouse on double-click If sngMouseY >= 0.89 * frmMDIGraph.chtMonthly.Height Then 'Mouse is below abscissa axis If sngMouseY <= 0.94 * frmMDIGraph.chtMonthly.Height Then 'Mouse is above bottom of labels If fTest Then 'Global constant fTest determines appearance of form With frmTestMonth .Show .txtMouseX = sngMouseX .txtMouseY = sngMouseY .txtMouseXPct = Format(sngMouseX / _ frmMDIGraph.chtMonthly.Width, "##0.00%") .txtMouseYPct = Format(sngMouseY / _ frmMDIGraph.chtMonthly.Height, "##0.00%") .txtWidth = frmMDIGraph.chtMonthly.Width .txtheight = frmMDIGraph.chtMonthly.Height intMonth = intTestDblClickMonth .txtmonthnum = intMonth End With Else intMonth = intTestDblClickMonth End If End If End If With frmMDIGraph.grdMonthly If intMonth > 0 Then 'select the column corresponding to the month number If fIsCrosstab Then .SelStartCol = intMonth + 1 Else .SelStartCol = intMonth - 1 End If .SelStartRow = 1 .SelEndRow = .Rows - 1 'Call the event-handler for the double-click on the grid grdMonthly_DblClick Else 'display the grid if double-click occurs elsewhere If .Visible Then .Visible = False Else .Visible = True End If SetGridPosition End If End With fInhibitResize = False End Sub
The .965 multiplier used in the intTestDblClickMonth function is a "fudge factor" used to trim the ordinate position calculation. You often find that you need to apply arbitrary scaling factors to mouse position calculations to take into account elements such as the width of the tick lines on the ordinate.
Another method of choosing the drill-down path is by clicking one of the wedges of a pie chart. The Graph custom control of the Professional Edition of Visual Basic 4.0 and the BPS Extended Graph custom control both include the ExtraData property that
enables you to explode the wedges of pie charts. You can use a single exploded wedge to emphasize a particular selection, or explode all of the wedges to alter the appearance of the pie chart as a whole. Figure 12.2 illustrates a pie chart for the Sales by
Product Category for a single month in 1994, with the Seafood product category wedge exploded. You explode a wedge by setting chtChart.ThisPoint = intWedge and then setting chtChart.ExtraData = 1. You return the wedge to its original
(imploded) position by setting chtChart.ExtraData = 0.
Figure 12.2. A pie chart with an exploded wedge.
The first of the two following sections show you how to use the Extended Graph control's Hot property and HotHit event to explode a wedge of a pie chart. The second section describes the Visual Basic code to duplicate the effect of the Hot property and
HotHit event without using the Extended Graph custom control.
The Extended Graph custom control adds the Hot property and the HotHit event to the graphs you create. If you enable the Hot property of the graph by setting the value of the Hot property to 1, a HotHit event is generated when you click or double-click
an active area of the graph or chart. An active area is a line segment of a line graph or an area of a pie, area, or bar chart. The HotHit event returns the values of two arguments, intHitSet and intHitPoint, which return the value of the data set and data
point corresponding to the position of the mouse when either click event triggers.
The DrawHotPieChart subprocedure in the Drill_cc.bas module is derived from the DrawPieChart subprocedure of the Graphs2.bas module described in the preceding chapter. You add the following two lines to the module to enable the HotHit event:
frmMDIPieChart.chtPieChart.Hot = 1 frmMDIPieChart.chtPieChart.DrawMode = 2
You need to have the BPS Extended Graph custom control installed to run the DrawHotPieChart subprocedure. If you have the Extended Graph custom control and want to use the Extended Graph Hot property and HotHit event in the examples that follow, you need to use the Drill_cc.vbp demo project on the accompanying CD-ROM. Use the Drill_cc.vbp project only if you have the Extended Graph custom control; otherwise, use the Drill_dn.vbp project.
Setting chtChart.Hot = 1 disables the Click and DblClick events of the chart. To retain the feature that enables the user to double-click the chart surface to return to the level 1 or level 2 graphs and charts, you need to detect a double-click
outside the active area of the pie chart. You can't add a timing function to the chtChart_MouseDown or chtChart_MouseUp events to check for a double-click. For some reason, Bits Per Second Ltd., also disables the MouseDown and MouseUp events
when the chart includes hot spots. The next section describes how to overcome this problem by writing code to detect a double-click on a pie chart created with the graph control of the Professional Edition of Visual Basic 4.0.
If you don't have the BPS Extended Graph control or you want to avoid using the Windows message queue to determine when double-clicks occur outside of the hot area, you can write your own code to emulate the Extended Graph control's hot spots on pie
charts. You'll need to remember your high-school trigonometry, however, to use the arctangent (Atn) function to determine which section of the pie chart the user double-clicked. Figure 12.3 is designed to help you recall some of the properties of
polar coordinates. (A pie chart is a stylized polar chart.) The first two values you need to determine are as follows:
Figure 12.3. Properties of a polar graph or chart.
The next step in writing the code is to determine the angles corresponding to the eight data points of the pie chart, and then compare the angle represented by the mouse position (relative to the origin of the pie) with the data point angles. As shown
in Figure 12.3, the four quadrants of a polar chart traditionally are numbered clockwise and are identified by Roman numerals. However, the chart control arranges the pie wedges in a counterclockwise direction, starting at 360 (0) degrees or 2 radians. For most readers, degrees are a more comfortable working unit than radians, so the example code in Listing 12.2 for the intTestHotHit function, which returns the number of the data point that is double-clicked, specifies wedge and
mouse-click angles in degrees.
The code in Listing 12.2 includes routines to determine the following values:
As with the code in Listing 12.1, the intTestHotHit function displays a small window in which the mouse coordinates and angle, relative to the origin of the pie chart in radians and degrees, appear in text boxes. The data displayed in TestTrig.frm is
indispensable when you are developing your code. You can turn off display of the test window by setting the value of the global constant fTest in the Declarations section of Drill_dn.bas to False. (Setting fTest = False runs the
production version of the intTestHotHit function, intPieHotHit, from a call contained in the chtPieChart_DblClick event handler.)
Listing 12.2. Code for the intTestHotHit function.
Function intTestHotHit(intMouseX As Integer, _ intMouseY As Integer) As Integer 'Purpose: Calculate geometric coordinates of mouse with ' respect to pie chart, explode selecte pie wedge 'Note: This is a development procedure Dim intOriginX As Integer 'Mouse X position Dim intOriginY As Integer 'Mouse Y position Dim intRadius As Integer 'Radius of pie chart Dim intRelX As Integer 'Mouse X relative to origin Dim intRelY As Integer 'Mouse Y relative to origin Dim intQuadrant As Integer 'Polar quadrants, 1-4 Dim sngPi As Single 'Value of Pi (3.1416) Dim lngSumData As Long 'Sum of data Dim intPoint As Integer 'Point number of hit Dim sngTheta As Single 'Hit angle in radians Dim intDegrees As Integer 'Hit angle in degrees Dim intCtr As Integer 'loop counter 'Calculate value of Pi sngPi = 4 * Atn(1) 'Create a local array to hold accumulative data ReDim lngData(frmMDIPieChart.chtPieChart.NumPoints) As Long intOriginX = frmMDIPieChart.chtPieChart.Width \ 2 intOriginY = frmMDIPieChart.chtPieChart.Height \ 2 - _ Int(0.025 * frmMDIPieChart.chtPieChart.Height) intRadius = Int(frmMDIPieChart.chtPieChart.Height / 5) intRelX = intMouseX - intOriginX 'positive right of origin intRelY = -intMouseY + intOriginY 'positive above origin If Sqr(intRelX ^ 2 + intRelY ^ 2) <= intRadius Then 'Mouse is within the perimeter of the pie intTestHotHit = True frmTestTrig.Show 'Determine the quadrant in which the double-click occurred 'Quadrants are I to IV, clockwise If intRelX >= 0 Then If intRelY >= 0 Then intQuadrant = 1 Else intQuadrant = 2 End If Else If intRelY >= 0 Then intQuadrant = 4 Else intQuadrant = 3 End If End If 'Get the angle from the arctangent of the mouse position '(Ratio of the opposite (X) to the adjacent (Y) sides) sngTheta = Atn(Abs(intRelX) / Abs(intRelY)) 'radians 'Convert radians to degrees within quadrant Select Case intQuadrant Case 1 intDegrees = sngTheta * 180 / sngPi Case 2 intDegrees = 180 - (sngTheta * 180 / sngPi) Case 3 intDegrees = 180 + (sngTheta * 180 / sngPi) Case 4 intDegrees = 360 - (sngTheta * 180 / sngPi) End Select 'Put the values in the TestTrig form frmTestTrig.txtRelX = intRelX frmTestTrig.txtRelY = intRelY frmTestTrig.txtTheta = sngTheta frmTestTrig.txtDegrees = intDegrees 'Loop to accumulate the data values, counter-clockwise For intCtr = frmMDIPieChart.chtPieChart.NumPoints To 1 Step -1 'Set the point, counter-clockwise frmMDIPieChart.chtPieChart.ThisPoint = intCtr 'Put the accumulated data from the point into the arry lngSumData = lngSumData + frmMDIPieChart.chtPieChart.GraphData lngData(intCtr) = lngSumData Next intCtr 'Loop to test the click angle versus the angle of the data wedge For intCtr = frmMDIPieChart.chtPieChart.NumPoints To 1 Step -1 'Convert the chart data into degrees lngData(intCtr) = lngData(intCtr) * 360 \ lngSumData If intDegrees <= lngData(intCtr) Then intPoint = intCtr Exit For End If Next intCtr 'Loop to explode selected wedge, implode the others For intCtr = 1 To frmMDIPieChart.chtPieChart.NumPoints frmMDIPieChart.chtPieChart.ThisPoint = intCtr If intCtr = intPoint Then frmMDIPieChart.chtPieChart.ExtraData = 1 Else frmMDIPieChart.chtPieChart.ExtraData = 0 End If Next intCtr DoEvents 'Redraw the chart with the exploded wedge frmMDIPieChart.chtPieChart.DrawMode = 2 DoEvents Else frmTestTrig.Hide End If End Function
Use twipsone twip equals one twentieth of a pointinstead of pixels or inches for calculations that refer to the mouse position. When you use twips, you can use integer arithmetic for most of your mouse position calculations. Using integer arithmetic results in applications that run substantially faster than those applications that use single- or double-precision floating point variables.
Figure 12.4 shows the exploded pie wedge and the frmTestTrig form that appears when you double-click the Seafood product category of the pie chart with the mouse in the position shown by the mouse pointer. To execute the code in Listing 12.2,
double-click one of the month abbreviations of the Sales by Product Category area chart (or one of the month column headers of the grid), and then double-click a wedge on the level 3 pie chart that appears.
Figure 12.4. The result of double-clicking a pie wedge using the code in Listing 12.2.
Using the code in Listing 12.2, instead of the BPS Extended Graph control's Hot property and HotHit event, results in an operation that is more consistent with the other graphs and charts that constitute the example application. The Extended Graph's HotHit event triggers if you single-click or double-click the wedgethe Extended Graph control disables Visual Basic's normal Click and DoubleClick events for Graph controls. This is inconsistent with other chart level changes that require a double-click. Using the code in Listing 12.2, you double-click the wedge to display the next level in the drill-down path. Single-clicking the wedge or graph has no effect.
You usually need to provide users with a choice of the detail data to display at levels that are lower in the hierarchy than the user can access with toolbar buttons. You can add another set of buttons if you have sufficient display area available.
Alternatively, you can use option buttons within a frame that also contains OK and Cancel command buttons to process or cancel the user's choice. (The GUI guidelines for Windows applications do not enable an option button choice to execute an action. Only
command buttons and menu choices execute actions.)
Figure 12.5 illustrates one approach to providing the user a choice of the detail information to display after clicking (with the Extended Graph control) or double-clicking (with the Extended Graph control or your own HotHit code) a wedge of the pie
chart. The 3D frame control (fraDrillOptions) is contained in a borderless but otherwise conventional (non-MDI) form (frmDrillOptions, Ddn_opts.frm) that is opened modally by adding the 1 parameter to the Show method. The code in Listing 12.3 performs the
following functions, which result in the display shown in Figure 12.5:
First, you need to add the following code to the DrawPieChart subprocedure of the Graphs3.bas module (derived from the Graphs2.bas module of the Printing sample application in the preceding chapter) to store the chart label text and save the position
of the left margin of the chart. The following is the added code:
'Fill the labels array (global) ReDim strLabels(frmMDIPieChart.chtPieChart.NumPoints) For intCtr = 1 To frmMDIPieChart.chtPieChart.NumPoints frmMDIPieChart.chtPieChart.ThisPoint = intCtr strLabels(intCtr) = frmMDIPieChart.chtPieChart.LabelText Next intCtr 'Save the position of the left margin of the chart (global) intPieChartLeft = frmMDIPieChart.chtPieChart.Left
You also need to alter the code of the intTestHotHit function (see Listing 12.2) to create the final intPieHotHit function. The primary changes involve determining the angle of the connector line between the chosen wedge and its label so that you can
position the chart and the frmDrillOptions form appropriately. The code that is changed to create intPieHotHit is as follows:
'Convert the chart data into degrees For intCtr = 1 To frmMDIPieChart.chtPieChart.NumPoints lngData(intCtr) = lngData(intCtr) * 360 \ lngSumData Next intCtr 'Loop to test the click angle versus the angle of the data wedge For intCtr = frmMDIPieChart.chtPieChart.NumPoints To 1 Step -1 If intDegrees <= lngData(intCtr) Then If intCtr < frmMDIPieChart.chtPieChart.NumPoints Then intWedgeAngle = Int(lngData(intCtr + 1) + _ (lngData(intCtr) - lngData(intCtr + 1)) \ 2) Else intWedgeAngle = Int(lngData(intCtr) \ 2) End If intHotHit = intCtr Exit For End If Next intCtr 'Loop to explode selected wedge, implode the others 'Remove the labels of the imploded wedges For intCtr = 1 To frmMDIPieChart.chtPieChart.NumPoints frmMDIPieChart.chtPieChart.ThisPoint = intCtr If intCtr = intHotHit Then frmMDIPieChart.chtPieChart.ExtraData = 1 frmMDIPieChart.chtPieChart.LabelText = strLabels(intCtr) Else frmMDIPieChart.chtPieChart.ExtraData = 0 frmMDIPieChart.chtPieChart.LabelText = "" End If Next intCtr DoEvents 'Remove the label lines from the pie chart frmMDIPieChart.chtPieChart.GraphStyle = 1 'Redraw the chart with the exploded wedge frmMDIPieChart.chtPieChart.DrawMode = 2 DoEvents 'Display the drill-down options form for level 4 ShowDrillOptions
Listing 12.3 shows the code needed to manipulate the pie chart and display the modal frmDrillOptions form. The ShowDrillOptions and RestoreLabels subprocedures are located in the Drill_dn.bas module. The Form_Load and cmdCancel_Click event handlers are
contained in Ddn_opts.frm.
Listing 12.3. Subprocedures for displaying the drill-down options form.
Sub ShowDrillOptions() 'Purpose: Redraw the chart, making room for the choice box If intWedgeAngle <= 180 Then 'Shift the chart to the right frmMDIPieChart.chtPieChart.Left = 0 Else 'Shift the chart to the left frmMDIPieChart.chtPieChart.Left = _ -(frmMDIPieChart.chtPieChart.Width - frmMDIToolbar.Width) End If 'Display the drill options form frmDrillOptions.Show 1 DoEvents End Sub Private Sub Form_Load() 'Purpose: Position the drill options form 'Note: This procedure must be in the Load event handler. ' Otherwise, the position is not set on the first ' change from left to right (or vice-versa). With frmDrillOptions 'Center form vertically .Top = frmMDIToolbar.Top + 1375 + _ (frmMDIToolbar.Height - 1375 - .Height) / 2 'Position the form horizontally depending on the wedge selected If intWedgeAngle <= 180 Then .Left = frmMDIToolbar.Left + 500 Else .Left = frmMDIToolbar.Left + _ frmMDIToolbar.Width - .Width - 500 End If End With End Sub Private Sub cmdCancel_Click() 'Purpose: Hide and unload the drill options form 'Note: Unload so that Form_Load is called on Show Me.Hide Unload frmDrillOptions 'Restore the labels of the pie chart RestoreLabels DoEvents 'Let it happen End Sub Sub RestoreLabels() 'Purpose: Restore the label text and graph position ' when cancel is selected or after displaying data Dim fRedraw As Integer 'True to redraw form Dim intCtr As Integer For intCtr = 1 To frmMDIPieChart.chtPieChart.NumPoints frmMDIPieChart.chtPieChart.ThisPoint = intCtr If frmMDIPieChart.chtPieChart.LabelText = "" Then fRedraw = True End If frmMDIPieChart.chtPieChart.LabelText = strLabels(intCtr) Next intCtr 'Restore the original left margin of the chart frmMDIPieChart.chtPieChart.Left = intPieChartLeft If fRedraw Then 'Restore the label lines to the pie chart frmMDIPieChart.chtPieChart.GraphStyle = 0 'Redraw the chart with all of the labels frmMDIPieChart.chtPieChart.DrawMode = 2 End If End Sub
Figure 12.6 shows the position of the pie chart and the Display Options window (frmDrillOptions) when you double-click a pie wedge whose wedge-to-label connecting line has an angle greater than 180 degrees. The connecting lines are made invisible in
the example shown in Figure 12.6 by setting the GraphStyle property to 1 in the intPieHotHit function.
Figure 12.6. An exploded pie chart with the wedge-to-label connecting lines removed.
Clicking the OK button on the frmDrillOptions form brings up a Grid control that displays the data you chose with the option buttons in the Display Options 3D frame. You need to translate the choices made in lower levels of the drill-down application
into an SQL statement that creates the Recordset that is used to populate the Grid control. The following are the four data items you need to translate into Jet SQL syntax:
The ANSI SQL statement that you need to display the product ID, product name, and sales volume data for a typical selection is as follows:
SELECT Products.[ProductID], Products.[ProductName], tblSalesProductMonth.Sales FROM Products, tblSalesProductMonth WHERE tblSalesProductMonth.[ProductID] = Products.[ProductID] AND tblSalesProductMonth.[CategoryID] = 1 AND tblSalesProductMonth.Year = "1994" AND tblSalesProductMonth.Month = "01" ORDER BY Products.[ProductID];
Figure 12.7 shows the result of executing the preceding SQL statement in the VisData application. The grid that you use to display the data on the drill-down grid form (frmDrill, Mdi_grid.frm) of the drill-down sample application resembles the SQL
Statement window of VisData.
Figure 12.7. The query result table for a typical drill-down grid.
Listing 12.4 displays the code you need to create SQL statements that are based on the current graph class and type, plus the month and year for which the drill-down data is desired. The application executes the GetDrillOptions subprocedure when you
click the OK button of the Display Options form (frmDrillOptions, Ddn_opts.frm). The graph class (intGraphClass) and type (intGraphType) determine the rollup data table that provides the data for the drill-down grid, grdDrill.
This version of the drill-down application supports only the first display option, Sales by Product Code, for the Sales and Orders chart class (intGraphClass = 1 or 3) and the Products chart type (intChartType = 1). If you choose a graph class or type for which a table is not available, you receive a Rolled up data is not available message. You can add additional chart types by creating rollup data tables based on market segment and sales region. Data that is arranged by customer, sales history, and other choices requires different SQL statements, but the basic structure of the statements remains the same as that shown in Listing 12.4.
Listing 12.4. Code to create SQL statements that populate the drill-down grid.
Sub GetDrillDetails() 'Purpose: Display a grid with the detail data on a form Dim dbDetail As Database 'New instance of GRAPHS.MDB Dim tblDefs As Recordset 'tblDefinitions Dim tblType As Recordset 'Table that supplies the category data Dim ssDetail As Recordset 'Created by SQL statement Dim frmDrill As Form 'Alias for frmMDIGrid Dim grdDrill As Control 'Grid that displays the data Dim strDataTable As String 'Name of data table Dim strDataField As String 'Name of data field of data table Dim strTypeTable As String 'Name of tblType Dim strTypeField As String 'Name of primary key field Dim strTypeName As String 'Name of legend field Dim strSQL As String 'Complete SQL statement Dim strSQLSelect As String 'SELECT statement Dim strSQLFrom As String 'FROM clause Dim strSQLWhere As String 'WHERE clause Dim strSQLOrderBy As String 'ORDER BY clause Dim strMonth As String 'Month of data (01...12) Dim strCode As String 'Code of current level 1/2 chart Dim strCaption As String 'Caption for the form Dim intCtr As Integer 'loop counter 'Set error handler On Error GoTo DrillDetailsError 'Create the graph code for the current level 1/2 graph strCode = LTrim(Str(intGraphClass)) & LTrim(Str(intGraphType)) 'Get the definition of the current graph Set dbDetail = OpenDatabase("c:\ddg_vb4\32_bit\chaptr10\graphs.mdb") Set tblDefs = dbDetail.OpenRecordset("tblDefinitions") tblDefs.Index = "PrimaryKey" tblDefs.Seek "=", strCode If tblDefs.NoMatch Then 'Error condition Exit Sub End If 'Specify the rollup table that suppies the data Select Case intGraphClass Case 1 'Monthly sales graph strDataTable = "tblSales" strDataField = "Sales" strCaption = "Sales of " Case 2 'Monthly margins graph strDataTable = "tblMargins" strDataField = "Margin" strCaption = "Margins of " Case 3 'Monthly orders graph strDataTable = "tblOrders" strDataField = "Orders" strCaption = "Orders for " Case 4 'Monthly backlog graph strDataTable = "tblBacklog" strDataField = "Backlog" strCaption = "Backlog of " Case 5 'Monthly cycle time graph strDataTable = "tblCycle" strDataField = "Days" strCaption = "Order Cycle Time of " Case 6 'Month-end inventory graph strDataTable = "tblInventory" strDataField = "Value" strCaption = "Ending Inventory of " End Select 'Months start in column 2 strMonth = LTrim(Str(frmMDIGraph.grdMonthly.SelStartCol - 1)) 'Create the SQL statement components strTypeName = "Legend" Select Case intGraphType Case 1 'Total or average line graphs (default) If intGraphClass = 2 Or intGraphClass = 5 Then strDataTable = strDataTable & "Average" Else strDataTable = strDataTable & "Total" End If 'Months start in column 0 strMonth = LTrim(Str(frmMDIGraph.grdMonthly.SelStartCol + 1)) Case 2 'Sales, margins, orders, backlog, cycle time and 'inventory by product category strDataTable = strDataTable & "Product" strTypeTable = "Categories" strTypeField = "[CategoryID]" strTypeName = "CategoryName" Case 3 'Sales, orders, and backlog by market segment If (intGraphClass = 1) Or (intGraphClass = 3) Or _ (intGraphClass = 4) Then strDataTable = strDataTable & "Segment" strTypeTable = "Segments" strTypeField = "[SegmentID]" End If Case 4 'Sales, orders, backlog, and cycle time by sales region If (intGraphClass = 1) Or (intGraphClass = 3) Or _ (intGraphClass = 4) Or (intGraphClass = 5) Then strDataTable = strDataTable & "Region" strTypeTable = "Regions" strTypeField = "[RegionID]" End If End Select 'Add "Month" to the name strDataTable = strDataTable & "Month" 'Open the data table table Set tblType = dbDetail.OpenRecordset(strTypeTable) tblType.MoveFirst 'Move to the entry for the chosen pie wedge For intCtr = 1 To intHotHit - 1 If intGraphType = 3 Or intGraphType = 4 Then 'Segments and Regions tables have unactivated records If Not tblType("Activated") Then 'Skip segments or regions not activated tblType.MoveNext If tblType.EOF Then Exit For End If End If End If tblType.MoveNext If tblType.EOF Then Exit For End If Next intCtr 'Fix the month number If Val(strMonth) < 10 Then strMonth = "0" & strMonth End If 'Finish the caption for the form strCaption = strCaption & tblType(strTypeName) & _ " for " & strMonths(Val(strMonth), 2) & _ ", " & strYear 'Create the SQL statements for the types and classes of graphs 'Note: This SQL statement only applies to product rollups strSQLSelect = "SELECT Products.[ProductID], " & _ "Products.[ProductName], " & _ strDataTable & "." & strDataField strSQLFrom = " FROM Products, " & strDataTable strSQLWhere = " WHERE " & strDataTable & _ ".[ProductID] = Products.[ProductID]" strSQLWhere = strSQLWhere & " AND " & strDataTable & _ "." & strTypeField & " = " & _ tblType(strTypeField) strSQLOrderBy = " ORDER BY Products.[ProductID];" 'These components of the WHERE clause apply to all monthly charts strSQLWhere = strSQLWhere & " AND " & strDataTable & _ ".Year = " & Chr(34) & strYear & Chr(34) strSQLWhere = strSQLWhere & " AND " & strDataTable & _ ".Month = " & Chr(34) & strMonth & Chr(34) 'Concatenate the elements of the SQL statement and create the snapshot strSQL = strSQLSelect & strSQLFrom & strSQLWhere & strSQLOrderBy 'following for debugging purposes, remove from production version Clipboard.SetText strSQL 'change error-handler to skip offending statement On Error Resume Next Set ssDetail = dbDetail.OpenRecordset(strSQL) 'Handle errors caused if all tables are not available If Err Then MsgBox prompt:=Err.Description & Chr(13) & _ "Rolled up data is not available.", _ buttons:=vbCritical, Title:="Drilldown Problem" 'remove error-handler On Error GoTo 0 RestoreLabels Exit Sub End If 'change error-handler to jump to label On Error GoTo DrillDetailsError If ssDetail.RecordCount = 0 Then 'Table missing or no records MsgBox prompt:="No records returned for this query.", _ buttons:=vbCritical, Title:="Drilldown Problem" RestoreLabels Exit Sub End If 'Change the values of these control variables to use another grid Set frmDrill = frmMDIGrid Set grdDrill = frmDrill.grdDetail 'Populate the grid with data from ssDetail PopulateGrid frmDrill, grdDrill, ssDetail, tblDefs, strCaption Exit Sub 'no more work DrillDetailsError: MsgBox prompt:=Err.Description, _ buttons:=vbCritical, Title:="Drilldown Error" End Sub
When the GetDrillDetails subprocedure creates an SQL statement that returns rows, the subprocedure calls the PopulateGrid subprocedure. The code of the PopulateGrid subprocedure determines the dimensions and content of the Grid control, grdDetail, on
the form dedicated to displaying the standard Grid control, frmMDIDrill (Mdi_Grid.frm). A typical example of a drill-down detail grid appears in Figure 12.8. To make the grid in Figure 12.8 appear, you need to do the following:
Stacking multiple layers of MDI child windows for drill-down applications is an effective method of displaying increasingly detailed information. You can review any prior window at level 2 or greater, and then return to the last window opened, by
choices from the window list of the Window menu. Advertisements for Windows decision-support applications often show multiple tiled or cascaded windows. Tiled windows don't provide the area to display significant amounts of information unless you have a
17-inch or larger video display unit. Cascaded windows tend to confuse users and also reduce the effective display area. As a rule, the maximized MDI child windows shown in the illustrations of this chapter are the preferred format for multi-layer,
decision-support applications.
Figure 12.8. The frmMDIGrid form that displays the drill-down data.
The code for the PopulateGrid subprocedure appears in Listing 12.5. The PopulateGrid subprocedure accomplishes the following primary tasks:
Listing 12.5. Code to populate, format, and position the drill-down data grid.
Sub PopulateGrid(frmDrill As Form, _ grdDrill As Control, _ ssDetail As Recordset, _ tblDefs As Recordset, _ strCaption As String) 'Load the drill-down grid form with data from ssDetails Dim lngDataTotal As Long 'Sum of data column for caption frmDrill.Show 'Load the form, if not loaded grdDrill.Visible = False 'Get the dimensions of the grid ssDetail.MoveLast 'Get an accurate record count grdDrill.Cols = ssDetail.Fields.Count grdDrill.Rows = ssDetail.RecordCount + 1 'Create an array to hold the text widths for each row and column ReDim intTextWidths(grdDrill.Rows, grdDrill.Cols) 'Add the field names to row 0 (fixed headings) For intCol = 0 To ssDetail.Fields.Count - 1 grdDrill.Row = 0 grdDrill.Col = intCol grdDrill.Text = ssDetail.Fields(intCol).Name 'Center the field names grdDrill.FixedAlignment(intCol) = 2 'Set the column width to 110% of the text width intTextWidths(0, intCol) = frmDrill.TextWidth(grdDrill.Text) * 1.1 Next intCol 'Populate the grid with the data ssDetail.MoveFirst For intRow = 1 To grdDrill.Rows - 1 'Iterate the rows grdDrill.Row = intRow For intCol = 0 To grdDrill.Cols - 1 'Iterate the columns and add data from the snapshot grdDrill.Col = intCol grdDrill.Text = ssDetail.Fields(intCol).Value 'Set the column alignment If intRow = 1 Then If intCol = 0 Then If InStr(grdDrill.Text, "ID") Then 'Center columns with ID grdDrill.ColAlignment(intCol) = 2 End If ElseIf intCol = grdDrill.Cols - 1 Then 'Right-align the last data column (numeric) grdDrill.ColAlignment(intCol) = 1 Else 'Left-align all other columns grdDrill.ColAlignment(intCol) = 0 End If End If If intCol = grdDrill.Cols - 1 Then 'Accumulate the total of the data If Not IsNull(ssDetail.Fields(intCol).Value) Then lngDataTotal = lngDataTotal + ssDetail.Fields(intCol).Value End If 'Format the data in the last column If tblDefs("Units") = "$" Then If intRow = 1 Then 'Add a dollar sign grdDrill.Text = Format(grdDrill.Text, "$#,##0") Else grdDrill.Text = Format(grdDrill.Text, "#,##0") End If ElseIf tblDefs("Units") = "%" Then If intRow = 1 Then 'Use percent symbol grdDrill.Text = Format(grdDrill.Text, "###.0%") Else grdDrill.Text = Format(Val(grdDrill.Text) / 100, "###.0 ") End If Else 'General number formatting grdDrill.Text = Format(grdDrill.Text, "#,##0") End If DoEvents End If 'Put the text widths in the array intTextWidths(intRow, intCol) = frmDrill.TextWidth(grdDrill.Text) Next intCol 'Go to the next record in the snapshot ssDetail.MoveNext Next intRow 'Size the columns of the grid by finding the maximum width For intCol = 0 To grdDrill.Cols - 1 For intRow = 0 To grdDrill.Rows - 1 'Set the width of the columns to 110% of the maximum text width If grdDrill.ColWidth(intCol) < _ (1.1 * intTextWidths(intRow, intCol)) Then grdDrill.ColWidth(intCol) = 1.1 * intTextWidths(intRow, intCol) End If Next intRow Next intCol 'Set the height of the grid grdDrill.Height = grdDrill.RowHeight(0) * (grdDrill.Rows * 1.08) 'Set the width of the grid grdDrill.Width = 0 For intCol = 0 To grdDrill.Cols - 1 grdDrill.Width = grdDrill.Width + grdDrill.ColWidth(intCol) Next intCol 'Position the grid in the center of the form grdDrill.Left = (frmDrill.ScaleWidth - grdDrill.Width) \ 2 grdDrill.Top = (frmDrill.ScaleHeight - grdDrill.Height) \ 2 'Turn grid scrollbars off grdDrill.ScrollBars = 0 'Display the form and the grid with a caption frmDrill.Caption = strCaption If lngDataTotal > 0 Then strCaption = strCaption & ", " & Format(lngDataTotal, "$#,##0") End If frmDrill.lblCaption = strCaption grdDrill.Visible = True DoEvents 'Highlight the totals column grdDrill.SelStartCol = grdDrill.Cols - 1 grdDrill.SelEndCol = grdDrill.Cols - 1 grdDrill.SelStartRow = 1 grdDrill.SelEndRow = grdDrill.Rows - 1 End Sub
There is no provision in the preceding code to handle grids that might exceed the current dimensions of the frmMDIGrid form, because the number of Northwind products in each category is limited. You can add a few lines to the code in Listing 12.5 to test the number of rows of the Recordset object, limit the height of the grid, and add a vertical scrollbar to access the hidden records. Similarly, if the width of the grid exceeds the current width of the form, you can limit the grid width and add a horizontal scrollbar.
You could save the pie chart step in the drill-down procedure if it were possible to click or double-click a hot area chart and return the category and month of the data you want to display. However, the intHitPoint argument needed to return the month
value is valid only for line, bar, high-low-close, GANTT, scatter, and tape graphs and charts. You don't get a valid intHitPoint argument when you use area charts. Therefore, if you want to bypass the pie chart and display detail data directly from a level
2 chart, you need to change the GraphType and GraphStyle properties to create a stacked bar chart. This section discusses substituting a BPS Extended Graph hot bar chart for an area chart.
You can use either a 2D or a 3D Extended Graph bar chart with the Hot property set to a value of 1 to display the frmMDIGrid form directly. Using a hot bar chart enables you to bypass the pie chart step in the drill-down sequence. An example of a 3D
bar chart that replaces the Sales by Product Category area chart appears in Figure 12.9.
Figure 12.9. A 3D bar chart showing sales by product category.
If you have the BPS Extended Graph control and want to experiment with hot bar charts, set the value of the global constant fUse3DBar in the Declarations section of the Drill_cc.bas of Drill_cc.vbp to True. With fUse3DBar = True, a hot 3D bar chart substitutes for the standard area chart. When you click a hot area of the 3D bar chart, the grid or spreadsheet that displays sales by product ID for the product category and month you choose appears without the intervening pie chart.
When you single-click an active region of an Extended Graph hot bar chart (the Extended Graph control doesn't distinguish between single- and double-clicks), the HotHit event returns both the category (data set, HitSet parameter) and month (data point,
HitPoint parameter). Listing 12.6 shows the event handling code you need to bypass the pie chart window and to display sales or orders of a particular category of products for the chosen month by product ID code directly.
Listing 12.6. The HotHit event handler to display the grid form.
Private Sub chtMonthly_HotHit(intHitSet As Integer, _ intHitPoint As Integer) 'Purpose: Process hot hits on various charts If frmMDIGraph.chtMonthly.GraphType <> 4 Then 'current chart can't be used to drill-down directly, show detail grid With frmMDIGraph.grdMonthly If .Visible Then .Visible = False Else .Visible = True End If End With SetGridPosition Else 'this chart is a bar chart, go directly to drill-down grid intHotHit = intHitSet With grdMonthly .SelStartCol = intHitPoint + 1 .SelEndCol = intHitPoint + 1 .SelStartRow = 1 .SelEndRow = grdMonthly.Rows - 1 End With DoEvents GetDrillDetails End If End Sub
When you use the Extended Graph control, Visual Basic's Click, DblClick, MouseUp, and MouseDown events for the control are disabled. The chtChart_HotHit event is only enabled when the Extended Graph's Hot property is set to 1 (that is, hot events are turned on). The HotHit event only fires when the mouse is clicked over one of the Extended Graph control's hot regions. For line charts, the hot regions are the endpoints of the line segments. For pie, area, and bar charts, the hot regions are the colored areas representing the graphed data. Mouse clicks on any part of an Extended Graph control that is not a hot region are ignored, and do not produce an event that you can capture with Visual Basic.
Database replication is the name given to the process of creating two or more copies of a database so that it is possible to keep all the objects and data in all copies of the database synchronized (that is, the databases can be
automatically updated so that all copies of the database contain the same objects and data).
Database replication is useful in a number of situations. You can use database replication any time you need to distribute copies of a database to other users. Companies with roving sales personnel who use laptop computers to enter orders use database
replication to synchronize laptop copies of databases with the central copies of the sales, orders, and products databases. Other companies use database replication to distribute copies of databases to local users to increase the speed of queries and other
information retrieval operations. For example, you may be able to increase the performance of a decision-support application by replicating rollup tables to a computer's local hard disk and then synchronizing the local copy of the database before running
your application. Queries and other operations on the local copies of the rollup tables are likely to be faster than operations executed using data on the network server, and synchronizing the copies of the rollup tables takes less time than making fresh
copies of the rollup tables.
The next few sections of this chapter explain the fundamental concepts of database replication and describe the basics of using the Jet database engine to replicate and synchronize databases.
Covering all aspects of database replication is beyond the scope of this book. Database replication is a relatively complex topic, and managing replicated databases is fairly code-intensivethis section covers only the most essential replication operations. Whenever possible, consider creating and managing database replicas with the Windows 95 Briefcase Replication features included with Access 95. If database replication is important to your application, you'll really need to test your replication strategies manually with Access 95 before trying to write your replication code in Visual Basic 4.0. You'll probably also need a copy of Microsoft's Access Developer's Kit, which contains the Replication Manager utility.
Database replication, as implemented in the Jet database engine, involves a relatively strict hierarchy. In Jet database replication, a replica set is a specific group of database copies that can be synchronized with each other. Every replica
set consists of a Design Master and one or more replicas. To create a replica set, you first create the Design Master for the replica set and then create at least one replica from the Design Master. You may then create additional
replicas from the Design Master or from other replicas in the replica set. A replica set may consist of only the Design Master database, or the Design Master and an unlimited number of additional replicas.
When you synchronize databases in a replica set, you synchronize any two (and only two) databases in the replica set at one time. You may synchronize a replica and the Design Master, or you may synchronize two replicas with each other. To synchronize
all of the databases in a replica set, you synchronize the databases two at a time, until all of the databases in the replica set have been synchronized with each other.
A replica set may have only one Design Master database at a time, although you may have an unlimited number of replicas. Typically, a replica set's Design Master is the first table created in the replica set. The Design Master is the only database in a
replica set in which you can change the design of objects such as tables, queries, and so on. Although it is possible to use Visual Basic code to change the properties of any replica database to make it a Design Master, you should only do so in order to
replace a damaged or corrupted Design Master database. Creating multiple Design Master databases in the same replica set may divide the replica set into two replica sets that cannot be synchronized with each other, resulting in a loss of data.
A replicable database is any database that can be used to create another copy of the database in the replica set. The Jet database engine determines whether or not a database is replicable (and hence part of a replica set) by examining the
Replicable property of the database. If the Replicable property contains the text value "T", then the database belongs to a replica set (even if the replica set consists of only that one database), and can be replicated to create additional
copies of the database in the replica set.
The Jet database engine keeps track of which specific replica set a replicable database belongs to through the database's ReplicaID property. The ReplicaID property contains a GUID number identifying a replica set. (GUID's, by definition, provide
globally unique identifying numbers; each replica set therefore has a unique identity via its ReplicaID property.) The Jet database engine uses the database's DesignMasterID property (which also contains a GUID number) to determine which database in the
replica set is the Design Master for that replica set.
Every database, whether or not it is part of a replica set, has the ReplicaID and DesignMasterID properties. Creating the Replicable property and adding it to the database's Properties collection causes the Jet database engine to convert a standard
database into a replicable Design Master. Adding the Replicable property to a database also causes the Jet database engine to add several new system tables to the database, and to add several new system fields to the tables in the database. These new
tables and fields store information about the other members of the replica set, changes made to the replica's data and structure, and information about synchronization activities. Table 12.1 lists the most important tables added to a replicable database,
while Table 12.2 lists the fields that are added to the tables in a replicable database.
Added Table | Purpose |
MSysErrors | Contains records describing synchronization errors. Records in MSysErrors indicate which operation failed (and why), which table was involved, which specific record produced errors, which replica(s) the error was encountered in, and which replica last changed the affected record. |
MSysExchangeLog | Provides historic information on replica synchronizations. This table contains information unique to each replica; it is not replicated to other databases in the replica set. |
MSysReplicas | Contains a list of the replicas in the replica set, including each replica's GUID full pathname, and UNC name. |
MSysSchChange | Contains historic information about changes made to the database's schema during synchronization operations. This table is not replicated to other databases in the replica set. |
MSysSchedule | Records in this table are used to schedule automatic synchronization and other activities with a particular replica. |
MSysSchemaProb | The Jet engine creates this table only if an error occurs while updating a replica's schema. MSysSchemaProb stores additional information about the cause of the error. |
MSysTableGuids | This table stores a list of table GUID numbers, and the names of the tables to which they correspond. |
name_Conflict | The Jet engine creates name_Conflict tables whenever a conflict between records in different replicas occurs while synchronizing the replicas. Name is the name of the table in which the conflict occurs. The conflict table stores copies of overridden records. The conflict tables store information unique to each replica and are not duplicated throughout the replica set. |
If you check the tables and fields listed in Tables 12.1 and Table 12.2 in MS Access, keep in mind that these are system objects. System objects are not normally displayed unless the System Objects check box is selected in the View tab of the Options dialog (accessible through the Tools menu command).
Added Field | Purpose |
Gen_Fieldname | Stores information about a group of changes for each OLE and Memo field in a table. Fieldname represents the name of the field. |
s_Generation | Stores a number indicating a particular group of changes (a "generation"). |
s_GUID | Stores a GUID number for that record. |
s_Lineage | Stores historic information about changes to a record. (This is a long binary field type.) |
You cannot change the values stored in the system fields (Table 12.2) of a replicated table. Similarly, except for the name_Conflict tables, you cannot edit records in any of the system tables listed in Table 12.1.
Probably the most useful of the replica system tables listed in Table 12.1 is the MSysReplicas table. This table contains one record for each table in the replica set, with fields for the replica's GUID, pathname, and UNC filename. Use the information
stored in the MSysReplicas table to locate other replicas in the replica set, and to identify the Design Master for the record set (as shown in Listing 12.9, later in this chapter). Table 12.3 lists the structure of the MSysReplicas table, showing each
field's name and data type.
Field Name | Type | Comments |
Description | Text | Contains a description of the replica database, provided by the user at the time the replica is created. |
IRecGen | Number | |
IRecGuid | Number | |
ISentGen | Number | |
ISentGuid | Number | |
LastExchange | Date/Time | The date and time this replica was last synchronized. |
LastScheduled Exchange | Date/Time | The date and time of the last scheduled synchronization. (The MS Access Developer's Kit is required for interactive event scheduling in replicated databases.) |
Machinename Nickname | Text Number | The network name of the computer on which this replica is stored. |
Pathname | Memo | The full pathname of the replica, including drive letter, directory, and filename. |
ReadOnly | Text | Whether the replica is read-only. |
Removed | Text | Whether the replica has been removed from the replica set. |
ReplicaID
ReplicaType |
AutoNumber
Number | The GUID of the replica set. |
SchemaGuid | Number | The GUID of the design master for this replica. |
SchemaVersion | Number | Tracks the number of times the replica's schema has changed. Used to help reconcile schema changes. |
TransporterID | Number | |
UNCPathname | Memo | The complete network UNC (Uniform Naming Convention) path to this replica. |
The following sections describe how to use Visual Basic code to create a new Design Master, make additional replicas in a replica set, and how to synchronize a replica with its Design Master. Additional sections describe how the Jet
database engine resolves conflicts between records in different replicas, and how you can install your own conflict resolution handler.
The examples in this section use the Replicas.vbp project in your \DDG_VB4\32_bit\Chaptr12 folder. A Jet 3.0 database, ReplSrc.mdb, is provided for you to experiment with. ReplSrc.mdb contains only one table with two fields; there is no data in the table. ReplSrc.mdb is not replicable; use the Replicas.vbp sample application to make replicable copies of ReplSrc.mdb.
The first step in creating any replica set is to create the replica set's Design Master. Any database that is not already part of a replica set may be converted to a Design Master. You convert a database to a replicable Design Master by adding the
Replicable property to the database's Properties collection and setting the value of the Replicable property to "T".
Converting a database to replicable form is a one-way street. Once you have made a database replicable, it is always replicableyou cannot convert it back to a non-replicable form. So that you may experiment safely, the Replicas.vbp sample application copies any database that you select for conversion to a Design Master and makes only the copy replicable.
Here are the essential steps for creating a new Design Master:
The Jet database engine detects the addition of the Replicable property and automatically makes all of the necessary modifications to the database structure so that the database can be replicated. At the same time, the ReplicaID and DesignMasterID
properties are assigned the GUID values for the new replica set.
When you run the Replicas.vbp sample application, it displays the dialog shown in Figure 12.10 (frmMain). Clicking the New Design Master button in the dialog displays a standard Open File dialog (the application uses a Common Dialog control,
cdlGetFileName) in which you select the database you want to convert to replicable form. After selecting the database from which to create a Design Master, you are then presented with a standard File Save As dialog in which you select the name for the new
Design Master database. The Replicas.vbp application prevents you from attempting to create a Design Master from a database that already has the Replicable property and ensures that the database copying operation does not overwrite any existing files on
the disk. The Replicas.vbp application also requires you to select only filenames that have the .mdb extension of a Jet database.
Figure 12.10. The main dialog of the Replicas.vbp application.
Listing 12.7 shows the code necessary to copy and then convert a database to replicable form. The cmdNewDesignMaster_Click event procedure is in the frmMain form of Replicas.vbp; the GetMDBFileName, IsReplicaDB, and FileExists functions are stored in
Replicas.bas.
The cmdNewDesignMaster_Click event procedure sets the SystemDB property of the DBEngine object to point to the System.mdw workgroup permissions database. Although not essential when creating a Design Master, establishing the correct permissions through the workgroup .mdw database is important if you want or need to later access any of the system tables in the replicable database. Replicas.vbp uses the default System.mdw file created by Access 95.
Listing 12.7. Creating a new Design Master.
Private Sub cmdNewDesignMaster_Click() 'Purpose: Creates a new Desing Master for replicated databases. Dim strDBSource As String 'name of Design Master source Dim dbSource As Database 'the database to be used as Design Master Dim strDBTarget As String 'target file name for new Design Master Dim dbTarget As Database 'new Design Master database Dim objProperty As Property On Error GoTo NewDesignMasterError 'set the SystemDB property to indicate which workgroup file to use. 'The workgroup file in this example is the Access default SystemDB DBEngine.SystemDB = "c:\msoffice\access\system.mdw" 'get name of database from which to create a Design Master strDBSource = GetMDBFileName(fOpen:=True, _ strTitle:="Select Source for New Design Master") 'did user cancel operation, or was there an error? If strDBSource = "False" Then MsgBox prompt:="Design Master creation canceled.", _ buttons:=vbInformation, Title:=mbxTitle Exit Sub End If 'don't use selected database if already a design master or replica Set dbSource = OpenDatabase(strDBSource) If IsReplicaDB(dbSource) Then MsgBox prompt:="The " & strDBSource & " database is" & _ " already part of a replica set," & _ " and can't be used to create a" & _ " new Design Master.", _ buttons:=vbInformation, Title:=mbxTitle dbSource.Close Exit Sub End If dbSource.Close 'close the database 'get the name of the new Design Master database. strDBTarget = GetMDBFileName(fOpen:=False, _ strTitle:="Select Destination for Design Master") 'did user cancel operation, or was there an error? If strDBTarget = "False" Then MsgBox prompt:="Design Master creation canceled.", _ buttons:=vbInformation, Title:=mbxTitle Exit Sub End If 'ensure that new database does not overwrite an existing file '(this also ensures that target name is not the same as the source) If FileExists(strDBTarget) Then MsgBox prompt:="The database " & strDBTarget & _ " already exists. Choose a different" & _ " folder or file name.", _ buttons:=vbInformation, Title:=mbxTitle Exit Sub End If 'copy the source database to its target FileCopy strDBSource, strDBTarget 'make target a Design Master by adding the Replicable property 'the first replica is always the Design Master of the replica set. 'First, open the database in exclusive mode (required) Set dbTarget = OpenDatabase(Name:=strDBTarget, _ Exclusive:=True) With dbTarget 'Next, create a new property object Set objProperty = .CreateProperty(Name:="Replicable", _ Type:=dbText, _ Value:="T") 'add new property to Properties collection .Properties.Append objProperty .Close 'close the database End With 'report success of operation MsgBox prompt:="Successfully created Design Master: " & _ strDBTarget & " from " & strDBSource, _ buttons:=vbInformation, _ Title:=mbxTitle & " - Create Design Master" Exit Sub NewDesignMasterError: MsgBox prompt:=Err.Description & Chr(13) & _ "Unable to create Design Master " & _ "from " & strDBSource & " to " & strDBTarget, _ buttons:=vbCritical, _ Title:=mbxTitle & " - Create Design Master" End Sub Function GetMDBFileName(fOpen As Boolean, _ strTitle As String) As Variant 'Purpose: Return a string containing the full path to an .mdb ' database file, or return False if the filename selection ' is canceled. 'NOTE: Uses the common dialog control cdlGetFileName on frmMain. ' The fOpen flag determines whether this function should use ' an open file or save file dialog. On Error GoTo FileNameError With frmMain.cdlGetFileName 'set up the dialog options .CancelError = True 'canceling dialog generates an error .DefaultExt = ".mdb" 'default file extension .Filter = "Jet/Access databases (*.mdb)|*.mdb" 'permit only .mdb files .DialogTitle = mbxTitle & " - " & strTitle If fOpen Then .Flags = .Flags Or cdlOFNFileMustExist End If .Flags = .Flags Or cdlOFNHideReadOnly End With If fOpen Then frmMain.cdlGetFileName.ShowOpen Else frmMain.cdlGetFileName.ShowSave End If 'set function return value from the filename property GetMDBFileName = frmMain.cdlGetFileName.filename Exit Function 'no more work FileNameError: If Err.Number <> cdlCancel Then MsgBox prompt:=Err.Description & Chr(13) & _ "Unable to obtain filename.", _ Title:=mbxTitle & " - Get FileName Error", _ buttons:=vbCritical End If GetMDBFileName = False 'this function failed to return a filename End Function Function IsReplicaDB(dbTest As Database) As Boolean 'Purpose: Return a Boolean value indicating whether a specified ' database object has the Replicable property Dim objProperty As Property 'iterate through the Properties collection For Each objProperty In dbTest.Properties If objProperty.Name = "Replicable" Then 'is the Replicable property set to True? If dbTest.Properties("Replicable") = "T" Then 'database is a replica; set function value and return IsReplicaDB = True Exit Function End If End If Next objProperty IsReplicaDB = False 'no Replicable property found End Function Function FileExists(strFName As String) As Boolean 'Purpose: Returns a boolean value indicating whether a file exists. 'NOTE: strFName is expected to contain a full path: disk, folder, and ' filename. Dim strTemp As String strTemp = Dir(Trim(strFName)) If Trim(strTemp) <> "" Then FileExists = True Else FileExists = False End If End Function
Although the Visual Basic 4.0 online help is correct when it states that the Replicable property must have a Text data type, it is not correct when it states that the Replicable property must be set to True in order to make a database replicable. Attempting to set the Replicable property to True results in "Invalid Argument" errors at runtime.
As correctly stated in the DAO online help files (Dao.hlp, supplied with Microsoft Access 7), setting the Replicable property to hold the single character "T" makes the database replicable. If you have MS Access 7, you should use the DAO help files provided with Access to get the most accurate information on database replication methods, properties, and objects.
The next section of this chapter shows you how to use the MakeReplica method to create additional replicas from your Design Master.
Once you have created the Design Masterthat is, the first replica of the new replica setyou can make additional replicas of the database by using the MakeReplica method. You can use the MakeReplica method to replicate any database in a
replica set, whether or not it is the Design Master for that replica set. Using the MakeReplica method creates a new database copy in the replica set.
If a database contains tables, queries, or other objects that you don't want to have replicatedthat is, you want to keep them local to that particular databaseyou can use the KeepLocal property to prevent a database object from being replicated. Use the CreateProperty method, if necessary, to create a KeepLocal property for the object, and set the property's value to "T." You must specify which objects to keep local to a database before replicating or synchronizing the database. Once an object in a database has been converted to replicable form, the KeepLocal property cannot be utilized.
Listing 12.8 shows all of the code needed to utilize the MakeReplica method. The cmdCreateReplica_Click procedure gets a filename from the user (with GetMDBFileName, shown in Listing 12.7), opens the database, verifies that the database is replicable,
gets a target filename, and then invokes the MakeReplica method. Both the pathname and Description arguments are required by the MakeReplica method. The pathname argument indicates the drive, folder, and filename for the new replica. Description is a text
string describing the new replica. The value passed in the Description argument is entered in the MSysReplicas table.
Listing 12.8. Using the MakeReplica method to create additional replicas.
Private Sub cmdCreateReplica_Click() 'Purpose: Creates a new replica table from a selected Design Master Dim strDBSource As String 'filename of source database Dim strDBTarget As String 'filename of new replica Dim dbSrc As Database On Error GoTo CreateReplicaError 'set the SystemDB property to indicate which workgroup file to use. 'The workgroup file in this example is the Access default SystemDB DBEngine.SystemDB = "c:\msoffice\access\system.mdw" 'get the name of a database file from which to create a replica strDBSource = GetMDBFileName(fOpen:=True, _ strTitle:="Select database for replication") 'drop out if user canceled file selection dialog If strDBSource = "False" Then Exit Sub 'open the source database Set dbSrc = OpenDatabase(strDBSource) 'prevent use of non-replicable databases If Not IsReplicaDB(dbSrc) Then MsgBox prompt:="'" & strDBSource & "' is not a" & _ " replicable database.", _ buttons:=vbCritical, _ Title:=mbxTitle & " - Creating Replica" GoTo BailOut End If 'get the name for the new replica strDBTarget = GetMDBFileName(fOpen:=False, _ strTitle:="Select new replica name") 'drop out if target name selection canceled If strDBTarget = "False" Then GoTo BailOut 'invoke the database's replication method dbSrc.MakeReplica pathname:=strDBTarget, _ Description:="replica of " & strDBSource 'report success of operation MsgBox prompt:="Replica '" & strDBTarget & _ "' successfully created from '" & _ strDBSource & "'.", _ buttons:=vbInformation, _ Title:=mbxTitle & " - Create New Replica" BailOut: On Error Resume Next dbSrc.Close Exit Sub 'no more work CreateReplicaError: MsgBox prompt:=Err.Description & Chr(13) & _ "Unable to create replica " & _ "from " & strDBSource & " to " & strDBTarget, _ buttons:=vbCritical, _ Title:=mbxTitle & " - Create New Replica" GoTo BailOut End Sub
You can only use the MakeReplica method if you have installed Briefcase Replication on your computer.
To synchronize two replicas in a replica set, you use the Synchronize method of one database and specify the full path and filename of the second database as an argument to the Synchronize method. You may optionally specify the exchange typethat
is, whether the synchronization operation should transfer data into or out of the database, or whether the synchronization should be bidirectional. To specify the direction of the synchronization transfer, use the predefined DAO constants:
Listing 12.9 shows the essential code needed to bidirectionally synchronize a replica with its Design Master. The cdmSyncReplica_Click procedure first gets a filename from the user and then checks to ensure that the selected database is replicable.
Next, the procedure checks to make sure that the user isn't trying to synchronize a Design Master with itself. The cdmSyncReplica_Click procedure then looks up the pathname to the replica's Design Master by searching through the MSysReplicas system table
until it finds the record whose ReplicaID field matches the selected replica's DesignMasterID property. The Pathname field yields the full path to the Design Master table, which is then passed to the Synchronize method.
If you attempt to synchronize a database opened for exclusive access, you will receive a runtime error message. You must open replica databases that you intend to synchronize in the same work session in shared mode.
Listing 12.9. Using the Synchronize method to synchronize replicas.
Private Sub cmdSyncReplica_Click() 'Purpose: Synchronize a replica to its design master Dim strDBSource As String 'name of database Dim dbSrc As Database 'database to be synchronized Dim rstSysReplicas As Recordset 'MSysReplicas table Dim strDMPath As String 'path to Design Master On Error GoTo SyncError 'set the SystemDB property to indicate which workgroup file to use. 'The workgroup file in this example is the Access default SystemDB 'This is an essential step -- the user permissions in the .mdw file 'are required in order to access the MSysReplicas system table. DBEngine.SystemDB = "c:\msoffice\access\system.mdw" 'get a replica database name strDBSource = GetMDBFileName(fOpen:=True, _ strTitle:="Select replica for synchronization") 'drop out if user canceled filename selection If strDBSource = "False" Then Exit Sub 'open the source database -- must be opened shared Set dbSrc = OpenDatabase(strDBSource) 'ensure that the selected database is a replica If Not IsReplicaDB(dbSrc) Then MsgBox prompt:="'" & strDBSource & "' is not a replica." & _ " Choose another database.", _ buttons:=vbInformation, _ Title:=mbxTitle & " - Sync Replica to Design Master" GoTo BailOut End If 'if DesignMasterID and ReplicaID are the same, then this database 'is the Design Master, and can't be synchronized with itself If dbSrc.DesignMasterID = dbSrc.ReplicaID Then MsgBox prompt:="'" & strDBSource & "' is the " & _ "Design Master of this replica set." & _ "You cannot synchronize the Design " & _ "Master with itself.", _ buttons:=vbInformation, _ Title:=mbxTitle & " - Sync Replica to Design Master" GoTo BailOut End If 'iterate through the MSysReplicas table to locate the 'Design Master's pathname Set rstSysReplicas = dbSrc.OpenRecordset("MSysReplicas") rstSysReplicas.MoveFirst strDMPath = "" Do 'is the ReplicaID field equal to the DesignMasterID property? If dbSrc.DesignMasterID = rstSysReplicas("ReplicaId") Then strDMPath = rstSysReplicas("Pathname") Exit Do End If rstSysReplicas.MoveNext Loop Until rstSysReplicas.EOF If Trim(strDMPath) = "" Then MsgBox prompt:="Design Master pathname not found.", _ buttons:=vbCritical, _ Title:=mbxTitle & " - Sync Replica to Design Master" GoTo BailOut End If 'use the Synchronize method to sync the replica to its design master dbSrc.Synchronize strDMPath 'announce successful completion of operation MsgBox prompt:="Synchronization of '" & strDBSource & _ "' with Design Master '" & strDMPath & _ "' completed successfully.", _ buttons:=vbInformation, _ Title:=mbxTitle & " - Sync Replica to Design Master" BailOut: On Error Resume Next dbSrc.Close Exit Sub SyncError: MsgBox prompt:=Err.Description & Chr(13) & _ "Unable to synchronize replica '" & _ strDBSource & "' to its Design Master.", _ buttons:=vbCritical, _ Title:=mbxTitle & " - Synchronize Replica" GoTo BailOut End Sub
When the Jet database engine sychronizes replicas, it normally uses its internal Conflict Revolver to reconcile change to the replicas. If a record has been changed in more than one replica, the Conflict Resolver accepts the record from the replica in which it was changed the greatest number of times. The name_Conflict tables are created and added to the replica whenever synchronization conflicts occur.
If you wish to write your own conflict resolution code, you may do soyou must write your custom conflict resolution code in Access VBA and store it in a module in the database, however. To replace the Jet database engine's inherent Conflict Resolver with one of your own, set the ReplicationConflictFunction property of the database to a string value containing the name of the Access VBA function you want to use for conflict resolution.
This chapter presented techniques you can use to expand the navigation and detail-data presentation capabilities of your decision-support applications. Detecting the mouse position when the user double-clicks a chart and then taking an action that is
determined by the position of the mouse pointer is a powerful navigation device that is not offered by most database front-end generator applications. Using dynamic graphic techniques, such as the exploding pie chart described in this chapter, makes your
decision-support applications visually interesting as well as informative.
This chapter also discussed the writing of code to create SQL statements to display detail data. Writing code to create SQL statements based on navigation choices is one of the most complex challenges you'll face when designing decision-support
applications that have drill-down capability.
This chapter also described the use of the Extended Graph control's hot 3D bar charts to populate a grid with rows that show sales of a chosen product and category for a given month.
The chapter concluded with an explanation of the essential concepts and coding techniques needed to create new replica sets, add replicas to a replica set, and synchronize a replica with its Design Master.
This is the last chapter in Database Developer's Guide with Visual Basic 4 that is devoted to decision-support applications. The next chapter, "Designing Online Transaction Processing Applications," shows you how to use Visual
Basic 4.0 and third-party custom controls to create data-entry applications that edit existing records or add new records to tables.