Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 13 -
Advanced Database Techniques


Many times, there's just one more thing that needs to be done for everything to work correctly. The How-To's in this chapter cover various advanced features of database programming that help polish your applications. The database back-up and replication features are particularly valuable because they can help you reduce support time and costs for your applications. A simple automated backup program in the Windows startup group can save hours of headaches after your application or customer has made a serious mistake.

13.1 Search for Database Records by Using a Soundex Algorithm

The Soundex feature available in many database management systems (DBMS) enables you to search for names of people, places, or streets without knowing the exact spelling. This How-To demonstrates using the Soundex function and points out some common problems with its use.

13.2 Back Up Selected Database Objects at a Set Schedule

The SQL SELECT...INTO statement provides a smooth way to perform a very selective online backup. This How-To uses an enhanced ActiveX timer control to periodically back-up selected database objects.

13.3 Replicate a Database by Using the Jet Engine

Access and the Jet engine enable you to copy databases and keep their contents synchronized in different locations. This How-To demonstrates creating and synchronizing a replicated database.

13.4 Omit Specified Objects from Replicas

By default, replication makes copies of everything in your database. This How-To demonstrates keeping some objects local when making a replica set.

13.5 Create a Nonreplicated Version of a Replicated Database

Strictly speaking, you cannot make a nonreplicated version of a replicated database, but you can remove a replica from the replica set and change the replica so that its structure can be modified. This How-To changes a replica database into a standalone database.

13.1 How do I...

Search for database records by using a Soundex algorithm?

Problem

The people answering the telephones in the order entry department sometimes have a difficult time understanding the names of individuals who call in. How can I write more forgiving SQL queries to look up names?

Technique

This How-To uses the Soundex function of Microsoft SQL Server. The sample SQL statements should also work with Sybase databases. Soundex is an encoding method for converting character strings into four digit codes. The goal is to provide a fuzzier search pattern for people trying find name-based data.

Steps

1. Connect to a SQL database from your workstation using a SQL command processor such as ISQL/W.

2. Issue the following commands to set up a test environment and open the file SOUNDEX.SQL from the How-To directory:

if exists (select * from sysobjects
    where id = object_id(`dbo.SoundexText') and sysstat & 0xf = 3)
    drop table dbo.SoundexTest
GO
CREATE TABLE dbo.SoundexTest (
    LastName char (20) NULL ,
    FirstName char (20) NULL
)
GO
INSERT INTO SoundexTest VALUES (`Brown', `Laura')
INSERT INTO SoundexTest VALUES (`Browne', `Laura')
INSERT INTO SoundexTest VALUES (`Brun', `Laura')
INSERT INTO SoundexTest VALUES (`Braun', `Laura')
INSERT INTO SoundexTest VALUES (`Broom', `Laura')
INSERT INTO SoundexTest VALUES (`Harper', `Bill');
INSERT INTO SoundexTest VALUES (`Harpster', `Bill');
INSERT INTO SoundexTest VALUES (`Hahpah', `Bill');
INSERT INTO SoundexTest VALUES (`Hobber', `Bill');
INSERT INTO SoundexTest VALUES (`Hopper', `Bill');
INSERT INTO SoundexTest VALUES (`Hooper', `Bill');
INSERT INTO SoundexTest VALUES (`Kennedy', `Dennis');
INSERT INTO SoundexTest VALUES (`Kenney', `Dennis');
INSERT INTO SoundexTest VALUES (`Kennealy', `Dennis');
INSERT INTO SoundexTest VALUES (`Kenney', `Dennis');
3. Issue the following SELECT statement to verify that all rows were inserted into the database. All rows should be retrieved and displayed as in
Table 13.1.

SELECT * FROM SoundexTest ORDER BY LastName, FirstName



Table 13.1. SELECT all rows results.

LAST NAME First Name
Braun Laura
Broom Laura
Brown Laura
Browne Laura
Brun Laura
Hahpah Bill
Harper Bill
Harpster Bill
Hobber Bill
Hooper Bill
Hopper Bill
Kennealy Dennis
Kennedy Dennis
Kenney Dennis
Kenney Dennis

4. Issue a SOUNDEX SELECT to find Brown. Table 13.2 shows all five Laura's.

SELECT * FROM SoundexTest
WHERE SOUNDEX (LastName) = SOUNDEX (`Brown')
ORDER BY LastName, FirstName

Table 13.2. SELECT SOUNDEX (`Brown') results.

LAST NAME First Name
Braun Laura
Broom Laura
Brown Laura
Browne Laura
Brun Laura

5. Use Soundex to find the various Harper records. Table 13.3 shows the results for various search targets.

SELECT * FROM SoundexTest
WHERE SOUNDEX (LastName) = SOUNDEX (`Harper')
ORDER BY LastName, FirstName
SELECT * FROM SoundexTest
WHERE SOUNDEX (LastName) = SOUNDEX (`Harp')
ORDER BY LastName, FirstName
SELECT * FROM SoundexTest
WHERE SOUNDEX (LastName) = SOUNDEX (`Hopper')
ORDER BY LastName, FirstName

Table 13.3. SELECT SOUNDEX results with different keys.

TARGET Last Name First Name
Harper Harper Bill
Harp No records returned
Hopper Hobber Bill
Hooper Bill
Hopper Bill

How It Works

The Soundex algorithm was established by the United States government to provide name searching for the Social Security Administration and the National Archives. The Soundex code is created by taking the first letter of the string and then adding values according to Table 13.4.

Table 13.4. Soundex coding guide.

CODE DIGIT Letter
0 All others or word too short
1 B, P, F, V
2 C, S, G, J, K, Q, X, Z
3 D, T
4 L
5 M, N
6 R

Table 13.5 shows the generated Soundex code and the last name for the sample data. Usually, Soundex will give a reasonable result, but it can make some interesting mistakes if the R is missed in a query. Notice in particular that the Soundex for Harper and Hopper don't match. Soundex can also be problematic if too few letters are provided for coding. Because all vowels and the silent letters H, Y, and W are dropped from the coding digits, a Soundex search for BR will not return any of our B names.

Table 13.5. Soundex coding results.

SOUNDEX CODE Last Name
B650 Brown
B650 Browne
B650 Brun
B650 Braun
B650 Broom
H616 Harper
H612 Harpster
H100 Hahpah
H160 Hopper
H160 Hobber
H160 Hooper
K530 Kennedy
K500 Kenney
K540 Kennealy

Comments

This sample showed a way to use the SOUNDEX function in queries. Unfortunately, these SELECT statements are very inefficient because they force the database manager to examine each row of the table. If SOUNDEX makes sense for your application, try to create a column whose value is the SOUNDEX of the target column, and then create an index on the SOUNDEX column.

13.2 How do I...

Back up selected database objects at a set schedule?

Problem

There are several tables in my database that need to be automatically backed up periodically. How can I force an automatic backup on a periodic basis?

Technique

The solution to this problem requires two core components: an enhanced timer and a database backup routine. The enhanced timer will be implemented as an ActiveX control that supports a "time to trigger" property and notification event. The database backup routine will create a new database and use a SELECT...INTO statement to implement the selective backup. The remainder of the project is a test form.

The extended timer control is required because the standard Visual Basic control only supports timers of approximately one minute in length. The extended timer control is driven by the TimeToTrigger property and will raise its ExtendedTimerPop event once per day at the specified time. The standard Visual Basic timer is used repeatedly and enabled until the time to trigger is within one minute of the current time. Then the Visual Basic timer is set to the precise number of seconds required to meet the TimeToTrigger property exactly.

Steps

Open the Backup.vbg project group to preview this project. Compile the enhanced timer control and then the Backup Test project. Run the Backup Test project (see Figure 13.1). Enter a near future time in the Trigger Time field and click the Start Timer button. Wait patiently until your system clock is one minute past the trigger time. Close the form and check in the application directory for a newly created backup copy of the Expense.MDB file whose name begins with the letters BU (for backup).

Figure 13.1. The Backup Test Form dialog box.

1. Create a new ActiveX Control project. Name the control ctlEnhancedTimer and the project prjEnhancedTimer.

2. Select the ctlEnhancedTimer control and set its InvisibleAtRunTime property to true.

3. Draw a single timer control on the object and name it tmrCheckIt. Save the project.

4. Select Add-Ins | Add-In Manager from the main menu and make sure that the VB 6 ActiveX Ctrl Interface Wizard is available in your environment by selecting it and checking Loaded/Unloaded in the Load Behavior frame. Select Add-Ins | ActiveX Control Interface Wizard from the main menu.

5. Click the Next button to proceed to the Select Interface Members dialog box (see Figure 13.2). Remove all names except the enabled property from the Selected Names window on the right.

Figure 13.2. The Select Interface Members dialog box.

6. Click Next to proceed to Create Custom Interface Members. Add the single property and event listed in Table 13.6 to the control.

Table 13.6. Properties, methods, and events for the extended timer control.

TYPE Name Description
Property TimeToTrigger Date/Time variable to contain the desired trigger time for timer.
Event ExtendedTimerPop Notifies the container that the TimeToTrigger has arrived.

7. Click Next to proceed to the wizard's Set Mapping dialog box. Select the public name Enabled and map it to the timer control's Enabled member, as shown in Figure 13.3.

Figure 13.3. Mapping the timer control member.

8. Click Next to set attributes for the extended timer control. Use the wizard's dialog box to set the TimeToTrigger property to be a Date variable with Read/Write capabilities at both runtime and design time.

9. Click Finish to have the wizard complete the skeleton code.

10. Add a Boolean to control variable to the Declarations section of the user control. This variable determines whether the last internal timer has been set for final event notification to the container.

Option Explicit
`Default Property Values:
Const m_def_TimeToTrigger = 0
`Property Variables:
Dim m_TimeToTrigger As Date
`Event Declarations:
Event ExtendedTimerPop()
` Private control variable
Private m_bLastInternalTimer As Boolean
11. Add the SetTimer helper procedure to set the internal timer control's interval property:

Private Function SetTimer() As Integer
` Determine if this is should be the last internal
` timer call
    Dim lDifference As Long
    lDifference = DateDiff("s", TimeValue(Now()), _
                    TimeValue(TimeToTrigger))
    If lDifference < 30 And lDifference > 0 Then
        ` This is the last timer to use
        m_bLastInternalTimer = True
        SetTimer = CInt(lDifference) * 1000
    Else
        ` Set timer for 30 more seconds
        m_bLastInternalTimer = False
        SetTimer = 30000
    End If
End Function
12. Add the following code to the timer event of the contained timer to implement a continuous, event-fired loop to achieve the desired long timer:

Private Sub tmrCheckIt_Timer()
` Handle the internal timer pop
    If m_bLastInternalTimer Then
        ` Notify the container
        tmrCheckIt.Enabled = False
        RaiseEvent ExtendedTimerPop
    Else
        ` Wait a while longer
        tmrCheckIt.Interval = SetTimer()
        tmrCheckIt.Enabled = True
    End If
End Sub
13. Add the following boldface code to the UserControl_InitProperties procedure to set the initial timer value:

Private Sub UserControl_InitProperties()
` Set up the timer variables
    ` Set the timer
    If Ambient.UserMode = True Then
        tmrCheckIt.Interval = SetTimer()
    End If
    m_TimeToTrigger = m_def_TimeToTrigger
End Sub
14. Add the following boldface code to the Property Let Enabled procedure. The interval must be set correctly for the container to enable the extended timer control at runtime.

Public Property Let Enabled(ByVal New_Enabled As Boolean)
    If Ambient.UserMode = True Then
        tmrCheckIt.Interval = SetTimer()
    End If
    tmrCheckIt.Enabled() = New_Enabled
    PropertyChanged "Enabled"
End Property
15. Add the following code to the Property Let TimeToTrigger procedure. The check for a valid date is required to prevent invalid calculations.

Public Property Let TimeToTrigger(ByVal New_TimeToTrigger As Date)
    ` Check that the new value is a valid date.
    If Not IsDate(New_TimeToTrigger) Then
        Err.Raise 380
        Err.Clear
        Exit Property
    End If
    m_TimeToTrigger = New_TimeToTrigger
    PropertyChanged "TimeToTrigger"
End Property
16. Add the following code to the UserControl_Resize procedure to keep the control approximately the same size as the timer control:

Private Sub UserControl_Resize()
` Resize the user control to a fixed size
    Size 420, 420
End Sub
17. Select File | Make EnhancedTimer.ocx from the Visual Basic main menu to compile the control. Correct any compilation errors.

18. Select File | Add Project from the main menu and add a new Standard EXE project to your Visual Basic environment. Name the default form frmBackupTest. Name the new project prjBackupTest. Save the project group and all its components. Name the project group Backup when prompted.

19. Test the design-time behavior of the control by drawing the control on the form and experimenting with resizing the control.

20. Add a text box, label, and command button to the test form and set the properties as listed in Table 13.7.

Table 13.7. Objects and properties for the Backup Test form.

OBJECT Property Setting
Form Name frmBackupTest
Caption Backup Test Form
CommandButton Name cmdStartTimer
Caption &Start Timer
TextBox Name txtTriggerTime
ctlEnhancedTimer Name ctlEnhancedTimer1
Label Name Label1
AutoSize -1 `True
Caption Trigger Time:

21. Add the following code to the cmdStartTimer_Click procedure to set the time to trigger and to enable the control:

Private Sub cmdStartTimer_Click()
` Start the timer
    If Not IsDate(txtTriggerTime.Text) Then
        MsgBox "Invalid time format"
    Else
        ctlEnhancedTimer1.TimeToTrigger = txtTriggerTime.Text
        ctlEnhancedTimer1.Enabled = True
    End If
End Sub
22. Add the following code to the ctlEnhancedTimer1_ExtendedTimerPop procedure to allow for control testing before beginning on the database backup code:

Private Sub ctlEnhancedTimer1_ExtendedTimerPop()
    Debug.Print "Extended timer pop"
End Sub
23. Compile and test the form. Be sure to save the project group.

24. In the project group window, make the prjBackupTest project active by highlighting it with the mouse. Select Project | Add Class Module from the Visual Basic main menu. When the Add Class Module dialog box appears, as in Figure 13.4, select VBCLASS.

Figure 13.4. The Add Class Module dialog box.

25. Select File | New Class from the Class Builder menu. Name the class clsBackupTable and click OK.

26. Highlight clsBackupTable in the Class Builder Classes window. Add the properties listed in Table 13.8 to the class by selecting File | New Property from the Class Builder menu or by right-clicking in the properties tab. Figure 13.5 shows the completed Properties tab.

Table 13.8. Properties for the clsBackupTable module.

NAME Data Type Declaration
TableName String Public Property
DatabaseObject Object Public Property

Figure 13.5. The completed Properties tab for clsBackupTable.

27. Click on the Class Builder Methods tab. Add a new method by selecting File | New method from the Class Builder menu or by right-clicking on the Methods tab. Name the method DoBackup and give it no arguments and no return data type.

28. Exit from Class Builder by selecting File | Exit from the menu. Update the project with your changes. Save the project group.

29. Add a reference to Data Access Objects by selecting Project | References from the Visual Basic main menu and checking Microsoft DAO 3.51 Object Library. Click OK to close the dialog box.

30. Add the following code to the DoBackup procedure to actually implement the table backup by creating a new .MDB file and copying the data:

Public Sub DoBackup()
Dim strDbName As String, strSql As String
Dim dbNew As Database
    On Error GoTo DbError
    ` Format a database name from the current time
 STRDBNAME = FORMAT(NOW, "BUYYYYMMMDDHHNNSS") & ".MDB"
    strDbName = App.Path & "\" & strDbName
    ` Make sure there isn't already a file with the name of
    ` the new database.
    If Dir(strDbName) <> "" Then Kill strDbName
    ` Create the new database
    Set dbNew = Workspaces(0).CreateDatabase(strDbName, _
                dbLangGeneral)
    ` Create a SQL command string to create the backup
    strSql = "SELECT " & TableName & ".* INTO " _
                & TableName & " IN `" & strDbName _
                & "` FROM " & TableName
    mvarDatabaseObject.Execute strSql
    Exit Sub
DbError:
    Err.Raise Err.Number, Err.Source, Err.Description
    Err.Clear
    Exit Sub
End Sub
31. Add the following declarations to the frmBackupTest form. These variables are required for the backup class.

Option Explicit
Private usrBackupTable As New clsBackupTable
Private dbOpenDb As Database
32. Add a Form_Load procedure to initialize the backup class:

Private Sub Form_Load()
Dim strDbName As String
    On Error GoTo DbError
    strDbName = App.Path & "\Expense.mdb"
    ` Open a database for the Backup class to use
    Set dbOpenDb = Workspaces(0).OpenDatabase(strDbName)
    Set usrBackupTable.Database = dbOpenDb
    usrBackupTable.TableName = "Expenses"
    Exit Sub
DbError:
    MsgBox Err.Description & " from " & Err.Source _
        & " Number = " & CStr(Err.Number)
    End
End Sub
33. Complete the ctlEnhancedTimer1_ExtendedTimerPop procedure by adding an invocation of the DoBackup procedure. Don't forget the error handler.

Private Sub ctlEnhancedTimer1_ExtendedTimerPop()
    On Error GoTo BackupError
    Debug.Print "Extended timer pop"
    usrBackupTable.DoBackup
   ` Restart the timer
    ctlEnhancedTimer1.Enabled = True
    Exit Sub
BackupError:
    MsgBox Err.Description & " from " & Err.Source _
        & " Number = " & CStr(Err.Number)
    Exit Sub
End Sub

How It Works

The first half of this How-To focuses on creating a timer control to support the backup application. In this case, the standard timer control does not provide the functionality needed to wake up at a specific time. This control's timeliness can be attributed to the SetTimer function specified in step 11. Every thirty seconds the control wakes up and checks to see how soon it should fire its event. If there is less than 30 seconds, the internal timer is adjusted to exactly the amount of time needed so that it wakes up on time.

On the application side of the How-To, a backup class handles the work of backing up a table. When the timer pops, the clsBackupTable's DoBackup method is invoked, and the timer is restarted. The Form_Load procedure initializes the backup class, and the button click starts the timer.

Comments

In addition to solving a particular problem, this How-To demonstrated the use of component programming to promote code reuse. The BackupTable class can have many uses outside of this project, especially if it was modified to allow multiple table names instead of just one. A variation on the BackupTable class could also be used in programs placed in the Windows Startup program group to provide an automatic backup every time Windows is restarted.

The enhanced timer control can simplify many aspects of your current timer programs because is works reliably for more than a minute. You might want to consider adding a schedule table to the enhanced timer control to allow for multiple events or a schedule such as daily, weekly, or monthly.

13.3 How do I...

Replicate a database by using the Jet engine?

Problem

The sales representatives in our company like to enter their expense reports on their laptop computers. How can I capture the expenses they enter in the field into the main expense database file?

Technique

Jet database replication enables you to create database copies and keep them synchronized. Both database design and contents are copied whenever the databases are synchronized. Replication creation requires that you make one database your Design Master and then invoke the MakeReplica method to create additional database copies. Use the Synchronize method to make the data consistent between two databases.

Steps

Open the project Replicate.vbp. Use the Browse buttons and select a source and replica database name (see Figure 13.6). Choose a database that can be modified; this How-To will make extensive changes. Click Create Replica to create a replica database. Use the VisData data maintenance utility (in the VB6 directory) to change a record in one of the databases. Click Synchronize. Use VisData to verify the change was propagated to the other database.

Figure 13.6. The Replicate Database form.

1. Create a new Standard EXE project in your workspace. Name the default form frmReplicate and the project prjReplicate. Save the form and project to disk.

2. Select Project | Components from the main menu and check Microsoft Common Dialog Control 6.0 (see Figure 13.7). Select Project | References from the Visual Basic main menu and activate the Microsoft DAO 3.51 Object Library by checking its box in the selection list.

3. Draw text boxes, labels, command buttons, and a Common Dialog control on the form as shown in Figure 13.6. Set the form's objects and properties as listed in Table 13.9.

Figure 13.7. Selecting the Microsoft Common Dialog Control 6.0.

Table 13.9. Objects and properties for the Replicate Database form.

OBJECT Property Setting
Form Name frmReplicate
Caption Replicate Database
CommandButton Name cmdOpenTo
Caption &Browse
CommandButton Name cmdSynchronize
Caption S&ynchronize
CommandButton Name cmdCreateReplica
Caption &Create Replica
CommandButton Name cmdOpenFrom
Caption &Browse
TextBox Name txtReplicaDbName
TextBox Name txtDbNameFrom
CommonDialog Name cdOpenFile
Filter Access Files (*.mdb)|*.mdb
Label Name Label1
AutoSize -1 `True
Caption Database to replicate:
Label Name Label2
AutoSize -1 `True
Caption Replica file name:

4. Add the following code to the Browse button procedures:

Private Sub cmdOpenFrom_Click()
` Open the Replicate from database file
    cdOpenFile.InitDir = App.Path
    cdOpenFile.ShowOpen
    txtDbNameFrom.Text = cdOpenFile.filename
End Sub
Private Sub cmdOpenTo_Click()
` Open the Replicate to database file
    cdOpenFile.InitDir = App.Path
    cdOpenFile.filename = "Replica.mdb"
    cdOpenFile.ShowOpen
    txtReplicaDbName.Text = cdOpenFile.filename
End Sub
5. Add the MakeReplicable procedure to the form's code. This procedure checks for the existence of the Replicable property, adds it, if needed, and sets the value to T to make the from database a Design Master.

Function MakeReplicable(ByRef dbMaster As Database) As Boolean
` Makes the passed database replicable
Dim prpReplicable As Property
Dim intIdx As Integer
Dim bFound As Boolean
    On Error GoTo DbError
    ` Check for existence of the replicable property
    For intIdx = 0 To (dbMaster.Properties.Count - 1)
        If dbMaster.Properties(intIdx).Name = "Replicable" Then
            bFound = True
            Exit For
        End If
    Next
    If Not bFound Then
        ` Create the property
        Set prpReplicable = dbMaster.CreateProperty( _
            "Replicable", dbText, "T")
        ` Append it to the collection
        dbMaster.Properties.Append prpReplicable
    End If
    ` Set the value of Replicable to true.
    dbMaster.Properties("Replicable").Value = "T"
    MakeReplicable = True
    Exit Function
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    MakeReplicable = False
    Exit Function
End Function
6. Add the CopyReplica helper function. This function actually creates a replica database using the from database as a source.

Function CopyReplica(ByRef dbMaster As Database, strRepName As _
   String) As Boolean
` Makes a replica database from the passed master
    On Error GoTo DbError
    ` If the target file exists, purge it
    If Dir(strRepName) <> "" Then Kill strRepName
    dbMaster.MakeReplica strRepName, "Replica of " & dbMaster.Name
    CopyReplica = True
    Exit Function
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    CopyReplica = False
    Exit Function
End Function
7. Add the following code to the cmdCreateReplica_Click procedure. This procedure gains exclusive control of the database, makes it a Design Master, and creates the specified replica. The bContinue Boolean prevents proceeding past an error.

Private Sub cmdCreateReplica_Click()
` Create a replica from the named database
Dim dbMaster As Database
Dim bContinue As Boolean
    On Error GoTo DbError
    ` Open the database in exclusive mode
    Set dbMaster = Workspaces(0).OpenDatabase(txtDbNameFrom.Text _
        True)
    ` Make the database the Design Master
    bContinue = MakeReplicable(dbMaster)
    ` Make the replica
    bContinue = CopyReplica(dbMaster, txtReplicaDbName.Text)
    dbMaster.Close
    Exit Sub
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    Exit Sub
End Sub
8. Add the code for the cmdSynchronize_Click procedure. This procedure synchronizes the contents of two databases in the same replica set.

Private Sub cmdSynchronize_Click()
Dim dbMaster As Database
    On Error GoTo DbError
    ` Open the database in non-exclusive mode
    Set dbMaster = Workspaces(0).OpenDatabase(txtDbNameFrom.Text _
        False)
    ` Synchronize the databases
    dbMaster.Synchronize txtReplicaDbName.Text, _
                    dbRepImpExpChanges
    dbMaster.Close
    Exit Sub
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    Exit Sub
End Sub

How It Works

When you replicate databases, Jet modifies the database properties and the structure of your tables substantially in order to track record and design changes. The Synchronize method uses these additional structures to track database design and data changes and apply the changes consistently over many databases.

The core concept of replication is the replica set governed by a single Design Master database. The Design Master is the place where all design changes must occur for all replicas. A database is a member of a replica set if it was created as a replica of the Design Master or as a replica of an existing replica. Any member of the replica set may synchronize data with any other member of the replica set. Figure 13.8 shows the basic relationships.

Figure 13.8. Replica set showing creation and synchronization.

Database Modifications

Jet makes several database modifications when you make a database replicable. When these changes are made, most of them are permanent.

The new database properties are Replicable, ReplicaID, and DesignMasterID. The Replicable property indicates that the database can be replicated and that the table structures have been modified. Once a database has been made replicable with the addition of the Replicable property, the ReplicaID is a unique identifier for this database file.

The DesignMasterID is a unique identifier specifying the database that can initiate all design changes for the replica set. Jet replication uses GUIDs (globally unique identifiers) wherever it needs a unique key for a database or field value. A GUID is a 16-byte generated value designed to be unique no matter where or when it is assigned. GUID values are usually displayed in hexadecimal notation with added hyphens for visual clarity. The database that is the Design Master has the same values for ReplicaID and DesignMasterID. Figure 13.9 shows the important database properties for a Design Master database as viewed by VisData application that ships with Visual Basic. Figure 13.10 shows the same properties for a replica. Note that the DesignMasterID GUID is the same as the ReplicaID GUID for the Design Master database. The replica database in Figure 13.10 has the ReplicaID of the Design Master database as its DesignMasterID. Equal values for ReplicaID and DesignMasterID are what define a database as a Design Master.

Figure 13.9. Database properties for a Design Master.

Figure 13.10. Database properties for a replica.

In addition to database property changes, making a database replicable adds about a dozen system tables to the database to track the Design Master, other replicas, table aliases, schema design problems, synchronization system errors, local replica-to-replica exchange logs, data conflicts, schedules, deleted records, and generation history. Because of all the property and system table changes, making a database replicable is a permanent operation. After the Replicable database property is set to T, it cannot be changed.

Table Modifications

In addition to database modifications, replication changes the table structures by adding three columns: a unique identifier, a generation identifier, and a lineage indicator. The unique identifier is stored in the s_GUID column and contains a globally unique identifier for this record in any replica.

The generation identifier is used to speed up incremental synchronization so that only changed records need to be sent to other replicas. Each time a record is changed, the generation number is set to zero. At synchronization time, all records with generation number zero are sent. The generation number is then set at both databases to one higher than the last generation number assigned. In addition, records with higher generation numbers are sent because they reflect more recent changes. Record additions and deletions are also reconciled between the two databases.

The lineage indicator is used to help resolve conflicts between simultaneous updates to data in different databases. When the same record is updated in two different replicas, a conflict can result. The Synchronize method resolves this conflict by using the s_Lineage field to determine which record will be placed into both databases. The losing database will have a copy of the losing record in a table named table_name_Conflict, where table_name is the original table name. For example, a conflict-losing record from the Expenses table would be saved at the losing location only into the Expenses_Conflict table.

Comments

Replication is a powerful tool in your database programming kit, but will require careful planning to implement successfully. Design considerations for replicated databases can become problematic because of our friend Murphy, whose famous law states, "If anything can go wrong, it will go wrong and at the worst possible time." I believe that Murphy might have been an optimist when it comes to complex replication schemes. Microsoft's core replication technology does a marvelous job of managing one-way replication. One-way implies that the data will be changed in only one location. With central administration, one-way data is the contents of list boxes, zip code tables, and product definitions that you want to push out to field laptops. For data collection, one-way means that a central site is collecting from multiple remote sites.

Two-way replication increases programming requirements because of the need to design and develop conflict-resolution programs and procedures. The placement of conflict losing records into only one database may make it difficult to implement conflict resolution programs in the desired location. For example, a sales automation application may want all conflict resolutions managed at the home office, but the Synchronize method might place a conflict notification into a sales rep's laptop in East Podunk. Hand-crafted code may then be required to find the remote conflicts and transport them back to the home office. One way to avoid conflicts is to use one-way replication only and separate tables into databases, depending on direction of data movement.

In addition to the synchronization itself, you should consider carefully your approach to how and when you plan to compact databases and run the RepairDatabase method. Compaction physically removes all deleted records from the database and assists with compaction of the generations of tables whose designs have changed. The RepairDatabase method inspects all system areas and tables for correctness and discards any incorrect data. A database that was open for write operations during a power failure or system crash can be left in a possibly corrupt state that should be repaired by the RepairDatabase method.

13.4 How do I...

Omit specified objects from replicas?

Problem

Replication is working great for my data collection application, but the experimental tables in the Design Master database keep getting replicated to the field. How can I omit undesired objects from the replicas?

Technique

The Jet KeepLocal property was designed to prevent database objects from being propagated during replication. Just set value KeepLocal to T in the database's property collection, and the object won't be replicated.

Steps

Open the project Replicate.vbp in the How-To04 directory (refer to Figure 13.06). Use the Browse buttons and select Expenses.mdb as the master database name and Replica.mdb as the replica database name. Click Create Replica to create a replica database. Use the VisData data maintenance utility (in the VB6 directory) to inspect the structures of the original and replica databases to verify that the replica contains only the Expenses table.

1. Start with the completed form from How-To 13.3.

2. Add the SetKeepLocal helper function to the form's code:

Function SetKeepLocal(dbTarget As Database, strCollName _
    As String, strObjectName As String) As Boolean
` Sets KeepLocal to "T" for the passed object.
    Dim intIdx As Integer
    Dim blnFound As Boolean
    Dim tdfTableDef As TableDef
    Dim prpProperty As Property
    Dim docDocument As Document
    Dim qdfQueryDef As QueryDef
    On Error GoTo ErrorHandler
    Select Case strCollName
        Case "Forms", "Reports", "Modules", "Scripts"
            Set docDocument = dbTarget.Containers(strCollName). _
                Documents(strObjectName)
            blnFound = False
            For intIdx = 0 To docDocument.Properties.Count - 1
                If docDocument.Properties(intIdx).Name _
                = "KeepLocal" Then
                    blnFound = True
                    Exit For
                End If
            Next intIdx
            If Not blnFound Then
                Set prpProperty = docDocument.CreateProperty _
                    ("KeepLocal", dbText, "T")
                docDocument.Properties.Append prpProperty
            Else
                docDocument.Properties("KeepLocal").Value = "T"
            End If
        Case "TableDefs"
            Set tdfTableDef = dbTarget.TableDefs(strObjectName)
            blnFound = False
            For intIdx = 0 To tdfTableDef.Properties.Count - 1
                If tdfTableDef.Properties(intIdx).Name _
                = "KeepLocal" Then
                    blnFound = True
                    Exit For
                End If
            Next intIdx
            If Not blnFound Then
                Set prpProperty = tdfTableDef.CreateProperty _
                    ("KeepLocal", dbText, "T")
                tdfTableDef.Properties.Append prpProperty
            Else
                tdfTableDef.Properties("KeepLocal").Value = "T"
            End If
        Case "QueryDefs"
            Set qdfQueryDef = dbTarget.QueryDefs(strObjectName)
            blnFound = False
            For intIdx = 0 To qdfQueryDef.Properties.Count - 1
                If qdfQueryDef.Properties(intIdx).Name _
                = "KeepLocal" Then
                    blnFound = True
                    Exit For
                End If
            Next intIdx
            If Not blnFound Then
                Set prpProperty = qdfQueryDef.CreateProperty _
                    ("KeepLocal", dbText, "T")
                qdfQueryDef.Properties.Append prpProperty
            Else
                qdfQueryDef.Properties("KeepLocal").Value = "T"
            End If
    End Select
    SetKeepLocal = True
    Exit Function
ErrorHandler:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    SetKeepLocal = False
    Exit Function
End Function
3. Remove the dbMaster.Synchronize call and add the following boldface code to the cmdCreateReplica_Click procedure:

Private Sub cmdCreateReplica_Click()
` Create a replica from the named database
Dim dbMaster As Database
Dim bContinue As Boolean
    On Error GoTo DbError
    ` Open the database in exclusive mode
    Set dbMaster = Workspaces(0).OpenDatabase(txtDbNameFrom.Text _
        True)
    ` Keep everything but the expenses table local
    bContinue = SetKeepLocal(dbMaster, "QueryDefs", _
                "ExpEmployeeNames")
    If bContinue Then _
        bContinue = SetKeepLocal(dbMaster, "QueryDefs", _
                        "ExpForOneEmployee")
    If bContinue Then _
        bContinue = SetKeepLocal(dbMaster, "TableDefs", _
                        "ObjectLibrary")
    ` Make the database the Design Master
    If bContinue Then _
        bContinue = MakeReplicable(dbMaster)
    ` Make the replica
    If bContinue Then _
        bContinue = CopyReplica(dbMaster, txtReplicaDbName.Text)
    dbMaster.Close
    Exit Sub
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    Exit Sub
End Sub

How It Works

When the KeepLocal property is set to T, the CreateReplica method doesn't create a replica of the database object. If you later want to replicate an object that was originally kept local, change its Replicable property to T in the Design Master and synchronize the replicas to propagate the additions. The VisData utility that shipped with Visual Basic can be used to make many of these changes.

Comments

In addition to the KeepLocal property, partial replication enables replicas to contain only part of the Design Master's data. To make a partial replica:

4 Use the TableDefs ReplicaFilter property and Relations PartialReplica property to specified the desired records.

13.5 How do I...

Create a nonreplicated version of a replicated database?

Problem

I've created a set of replica databases, but I want to allow one copy to be modified by another developer in her test environment. How can I cut a replica out of the replica set so that it can be changed independently of the others?

Technique

Within a set of replicas, only one database is allowed to have design changes, such as table creation or field size changes. All other replicas must have their design changes made at the Design Master database and synchronized through the replica set. This strict requirement ensures that the Data Access Objects Synchronize method can adequately track and implement changes. After all, "Too many cooks spoil the broth." But there are times when a replica needs to be cut out of the replica set. The reasons might include testing a new replication schema without affecting the existing replica set, quality assurance testing of the replication methods, or a major application design change. The core technique for returning design independence to a replica database for structure changes is to make the replica into a new Design Master.

Steps

Open the project BreakReplica.vbp (see Figure 13.11). Select Replica.mdb as your database. Click Break Replica. Verify so that you can modify table definitions using the VisData utility.

Figure 13.11. The Break Replica Set form.

1. Create a new Standard EXE project in your workspace. Name the default form frmBreakReplica and the project prjBreakReplica. Save the form and project to disk.

2. Select Project | Components from the main menu and check Microsoft Common Dialog Control 6.0. Select Project | References from the Visual Basic main menu and activate the Microsoft DAO 3.51 Object Library by checking its box in the selection list.

3. Draw controls on the form so that it looks like Figure 13.12. Don't forget to add a Common Dialog control. Set the form's objects and properties as listed in Table 13.10.

Table 13.10. Objects and properties for the Break Replica Set form.

OBJECT Property Setting
Form Name frmBreakReplica
Caption Break Replica Set
CommandButton Name cmdBreakReplica
Caption &Break Replica
CommandButton Name cmdOpenFrom
Caption &Browse
CommonDialog Name cdOpenFile
Filter Access Files (*.mdb)|*.mdb
TextBox Name txtDbNameFrom
Label Name Label1
AutoSize -1 `True
Caption Database to remove from set:

4. Add the following code to the cmdOpenFrom_Click procedure to select a filename:

Private Sub cmdOpenFrom_Click()
` Open the Replicate from database file
    cdOpenFile.InitDir = App.Path
    cdOpenFile.ShowOpen
    txtDbNameFrom.Text = cdOpenFile.filename
End Sub
5. Add the following code to the cmdBreakReplica_Click procedure to set the selected database's DesignMasterID to itself:

Private Sub cmdBreakReplica_Click()
Dim dbReplica As Database
    On Error GoTo DbError
    ` Open the database in exclusive mode
    Set dbReplica = Workspaces(0).OpenDatabase(txtDbNameFrom.Text _
        True)
    dbReplica.DesignMasterID = dbReplica.ReplicaID
    dbReplica.Close
    Exit Sub
DbError:
    MsgBox Err.Description & " From: " & Err.Source _
            & "Number: " & Err.Number
    Exit Sub
End Sub

How It Works

To remove a database from the restrictions placed on replicas, you simply need to make the database replica its own Design Master by setting the DesignMasterID to the ReplicaID as shown in step 5. The database will be immediately cut out of the replica set and will no longer be easily able to synchronize data structure or content changes with its previous replica siblings. In other words, breaking a database out of a replica set should be considered permanent. In a production environment, the break-up could be very inconvenient if done incorrectly.

Replicated databases remain replicated with all the extra properties and system tables after the database's Replicable property is set to T. How-To 13.3 discusses the changes made to the database when it is converted to a replica. Breaking a database out of a replica set will not make the database nonreplicable; it will allow only its own design changes. All the extra data will still be in the database.

Comments

Replication is a fairly permanent decision, but it can be a powerful tool, especially with the use of the partial replication methods to propagate design changes with very little coding effort. A package being distributed to many locations can use replication to propagate design changes while never transmitting any data. Field database design changes can be managed by distributing a new, empty member of the replica set with the required design changes and synchronizing with a replica filters set to pass no records.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.