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.
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:
<=
sngTheta <= 2).
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.
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-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.
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 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:
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 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.
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.
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.
