Previous Page TOC Next Page



- 12 -
Extending Your Use of the Data Access Object


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 control—standard 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 library—Graph32.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.


Using MouseDown and Double-Click Events to Activate Drill-down


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.


Choosing the Drill-Down Path with an Exploding Pie Chart


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.

Using ChartBuilder's HotHit Event with Pie Charts


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.

Writing Your Own HotHit Code for a Pie Chart


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 twips—one twip equals one twentieth of a point—instead 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 wedge—the 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.


Giving the User Drill-Down Options


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:

Figure 12.5. Adding a modal detail data option form to an exploded pie chart with label leader lines.

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.

Populating a Grid with the Selected Data


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:

  1. Click the Orders button to display the monthly orders line graph at level 1.

  2. Click the Products button to display the Orders by Product Category area chart at level 2.

  3. Double-click the Dec(ember) chart label (or display the grid and double-click the Dec(ember) grid column header) to display the Orders by Product Category for 1994—December pie chart (level 3).

  4. Double-click the Beverages category of the pie chart (level 4) to explode the pie wedge and show the Display Options form (level 5).

  5. Choose Sales by Product Code and click the OK button of the Display Options form to show the grid (level 6).

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.


Setting the Drill-Down Path Directly from a Level 2 Chart


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.


Replicating Jet 3.0 Databases with the Data Access Object


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-intensive—this 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.


Understanding Database Replication


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.

Table 12.1. Important tables added to replicable databases.

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


Table 12.2. Fields added to replicable tables in replicable databases.

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.

Table 12.3. Structure of MSysReplicas

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.


Creating a Database Design Master


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 replicable—you 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:

  1. Open the database.

  2. Use the CreateProperty method to create the Replicable property. The Replicable property must have a Text data type (specified by the Visual Basic dbText constant) and must contain the value "T".

  3. Use the Append method of the database's Properties collection to add the new Replicable property to the database.

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.

Creating Replica Databases


Once you have created the Design Master—that is, the first replica of the new replica set—you 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 replicated—that is, you want to keep them local to that particular database—you 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.


Updating a Replica from the Design Master


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 type—that 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 so—you 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.



Summary


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.

Previous Page Page Top TOC Next Page