
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.
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.
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.
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.
By default, replication makes copies of everything in your database. This How-To demonstrates keeping some objects local when making a replica set.
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.
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
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');
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
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
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.
| 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.
| 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.
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.
Figure 13.2. The Select Interface Members dialog box.
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.
Figure 13.3. Mapping the timer control member.
Option Explicit `Default Property Values: Const m_def_TimeToTrigger = 0 `Property Variables: Dim m_TimeToTrigger As Date `Event Declarations: Event ExtendedTimerPop() ` Private control variablePrivate m_bLastInternalTimer As Boolean
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 IfEnd Function
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 IfEnd Sub
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_TimeToTriggerEnd Sub
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
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
Private Sub UserControl_Resize() ` Resize the user control to a fixed size Size 420, 420End Sub
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:
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 IfEnd Sub
Private Sub ctlEnhancedTimer1_ExtendedTimerPop() Debug.Print "Extended timer pop"End Sub
Figure 13.4. The Add Class Module dialog box.
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.
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 timeSTRDBNAME = 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 SubEnd Sub
Option Explicit Private usrBackupTable As New clsBackupTablePrivate dbOpenDb As Database
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) EndEnd Sub
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 SubEnd 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.
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.
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:
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.filenameEnd Sub
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 FunctionEnd Function
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 FunctionEnd Function
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 SubEnd Sub
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 SubEnd 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.
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.
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 FunctionEnd Function
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 SubEnd 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.
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.
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:
Private Sub cmdOpenFrom_Click() ` Open the Replicate from database file cdOpenFile.InitDir = App.Path cdOpenFile.ShowOpen txtDbNameFrom.Text = cdOpenFile.filenameEnd Sub
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 SubEnd 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.
© Copyright, Macmillan Computer Publishing. All rights reserved.