Navigation:  Articles > Feb-1999 >

Introducing the TSI Synchronizer Object

Previous pageReturn to chapter overviewNext page

Mary Chipman and Michael Kaplan          
 
Developers have long yearned for a way to programmatically control the Replication Manager. At last, relief is at hand. Michael Kaplan has created the TSI Synchronizer Object, and this article will tell you how to use it.
 

"Replication was removed from the .ACCDB file format in Access 2007 but on the occasion that I used TSI Software, it worked well"  Garry

 
Jet Replication is one of the most powerful features in Microsoft Access for creating distributed applications. The most common scenario for a replicated application involves remote sites that are loosely connected either by WAN or dialup connections. However, implementing replication with a direct dialup connection is risky, since, if the connection is unexpectedly dropped in the middle of synchronization, the replicas are likely to become corrupted. To avoid this situation, Microsoft Access 97 has provided the ability to configure indirect replication by using the Replication Manager. With indirect replication, you set up folders that serve as drop boxes, and no direct connection between the replicas is required. In addition to setting up indirect replication, you also need the Replication Manager to set up Internet synchronizations, to schedule synchronizations, to view synchronization log history, and to perform other maintenance tasks. However, the Replication Manager has the following limitations:

It has no programmatic interface -- you can't control it by writing VBA code.
You can't use the Replication Manager to perform one-way synchronizations (either importing or exporting changes only). You must write VBA code to do this.
The Replication Manager can't dial up a remote connection -- you must already have established a connection for scheduled synchronizations.

 
By far the greatest limitation with the Replication Manager has always been the inability to write code to control it. This problem is now solved with the new TSI Synchronizer 3.52 object included in the accompanying Download file. The TSI Synchronizer works with Jet 3.5 and can be called from any VBA host. The TSI Synchronizer version 4.0 will be available when Access 2000 is released and will work with Jet 4.0. You can also download the TSI Synchronizer from http://www.Trigeminal.com. The TSI Synchronizer is freeware, so there's no charge (other than the charges applied by your ISP for the time it takes to download). The self-extracting file is around 1200K and contains all of the necessary DLLs and a Help file, which are installed in your System directory.
 
The TSI Synchronizer allows you to perform the following tasks in code, with or without the Replication Manager:

Retrieve and set the replica retention period
Perform indirect synchronizations
Perform direct or Internet synchronizations
Retrieve history information about synchronizations
Create new replicas
Manage synchronization scheduling
Control the replicas managed by different synchronizers
Manage remote synchronizers on multiple machines
Implement progress meters in code with status events
View information unavailable elsewhere about replicas and the replica set
Set synchronizer properties

 
Using the TSI Synchronizer
To use the TSI Synchronizer, set a reference in your project references to TSI Synchronizer 3.52 or directly to the Synch35.dll in the System directory. The TSI Synchronizer objects, methods, properties, and events will be visible using the Object Browser, as shown in Figure 1. We'll cover the main ones in this article. The online Help file, Synch.hlp, is located in your System folder.

199902_MC1 Figure 1
 
The TSI Synchronizer object has the methods and properties shown in Table 1.
 
Table 1. TSI Synchronizer methods and properties.

Methods

Properties

About

DatabaseName

GetFullSchedule

DesignMasterID

HistoryItems

IndirectDropbox

MakeReplica

IndirectDropboxPwd

MoveReplica

InternetFtpDropbox

SetFullSchedule

InternetHttpDropbox

SetSimpleSchedule

InternetHttpDropboxPwd

SynchDirect

IsDesignMaster

SynchIndirect

InternetServer

SynchInternet

LogFile

ManagedReplicas

 

Name

 

Password

 

ReplicaID

 

ReplicaSet

 

RetentionPeriod

 

Running

 

SystemDB

 

UID

 

Version

 

 
 
Creating replicas
If you're starting from scratch and want to convert a regular database to a Design Master, you can use the object's MakeReplica method to do so. MakeReplica will convert an existing database to a Design Master and create a new replica at the same time, much like using the menu commands. The following function uses the MakeReplica method by accepting the name of the database you want to convert to a Design Master and the name of the replica that's to be created from it:
 

Public Function CreateNewReplica( _

strFromReplica As String, _

strNewReplica As String, _

Optional strDescription As String = "", _

Optional lngPriority As Long = -1, _

Optional fPartial As Boolean = False, _

Optional fReadOnly As Boolean = False) As Boolean

 

If moSync Is Nothing Then

  Set moSync = New Synchronizer

End If

' Setting the DatabaseName property converts a

' database to a replica if it isn't one already.

moSync.DatabaseName = strFromReplica

' The MakeReplica method creates an additional replica.

moSync.MakeReplica strNewReplica, strDescription, -

 lngPriority, fPartial, fReadOnly

moSync.DatabaseName = ""

CreateNewReplica = True

 

End Function

 
 
In addition to creating a normal replica as shown previously, you can also use the additional optional arguments of the MakeReplica method to provide a description, set the priority of the replica, create a partial replica, or create a read-only replica.
 
Synchronizing replicas
Once you've created a couple of replicas to work with, you can then synchronize them using the SynchDirect method of the TSI Synchronizer object. There are two steps to using SynchDirect. First, you set the DatabaseName property to point to the Source database, and then you use the SynchDirect method to perform the synchronization. You can also provide an additional argument for the direction, which corresponds to the Synchronize method's ExchangeType parameter in DAO. There are three enumerated constants available to provide the direction, as shown in Table 2.
 
Table 2. SynchDirect constants.

Constant

Value

What it does

SynchTypeReplExport

0

Export changes only

SynchTypeReplImport

1

Import changes only

SynchTypeReplImpExp

2

Bi-directional synchronization

 
 
SynchTypeReplImpExp is the default, so if you don't specify one of the others, your synchronization will be performed bidirectionally:
 

moSync.DatabaseName = "C:\Source.MDB"

moSync.SynchDirect "D:\Destination.MDB", SynchTypeReplImpExp

 
 
Using the HistoryItems collection
The TSI Synchronizer uses the HistoryItems collection of HistoryItem objects to store information about each synchronization between replicas. The properties of the HistoryItem object are listed in Table 3.
 
Table 3. HistoryItem properties.

Property

Action

InsertsReceived

The number of inserts received during the synchronization.

InsertsAndUpdatesSent

The combined number of inserts and updates sent during the synchronization.

Conflicts

The number of conflicts that were generated during the synchronization.

ConnectionType

The type of connection (synchConnTypeDirect, synchConnTypeIndirect, or synchConnTypeInternet) of the synchronization.

DataFlow

The type of dataflow (synchDataflowSend, synchDataflowReceive, synchDataflowSendReceive, or synchDataflowTest) of the synchronization.

DeletesReceived

The number of deletes received during the synchronization.

DeletesSent

The number of deletes sent during the synchronization.

DesignChangesReceived

The number of design changes received during the synchronization.

DesignChangesSent

The number of design changes sent during the synchronization.

ErrorCode

Error code returned during synchronization. See the online Help file for a complete list of enumerated error codes.

Errors

The number of schema/data errors that were generated during the synchronization.

ErrorText

Text associated with the error specified by ErrorCode.

InitiatedDate

The date and time that the synchronization began.

LastAction

This string property will tell you the last action that has occurred during this synchronization. It's most useful for synchronizations that are either in progress or that failed for some reason.

RemoteReplicaName

The name of the remote replica.

ReplicaName

The full (UNC) path and file of the local replica.

Scheduled

A Boolean flag that indicates whether or not this was a scheduled synchronization.

SynchStatus

The status of the synchronization (synchStatusRequested, synchStatusInProgress, synchStatusCompleted, synchStatusFailed, or synchStatusCanceled).

TableModifications

The number of schema changes that occurred during the synchronization.

UpdatesReceived

The number of updates received during the synchronization.

 
 
The following function, SyncMe, takes as arguments the names of the two databases and an optional argument for the synchronization mode. It then prints the history items to the Debug window and logs them to tblHistoryItems. The fields in tblHistoryItems have been given the same names as the HistoryItem properties. The task of creating an attractively formatted report based on the tblHistoryItems table is left to the reader.
 

Public Function SyncMe(strFromDatabase As String, _

   strToDatabase As String, _

   Optional lngMode As Long = 2) As Long

 

Dim hsts As HistoryItems

Dim hst As HistoryItem

 

Dim db As Database

Dim strSQL As String

 

Const QT = """"

 

If moSync Is Nothing Then

  Set moSync = New Synchronizer

End If

moSync.DatabaseName = strFromDatabase

moSync.SynchDirect strToDatabase, lngMode

 

Set hsts = moSync.HistoryItems

Set hst = hsts(hsts.Count)

 

Debug.Print hst.Conflicts, "--Conflicts"

Debug.Print hst.Errors, "--Errors"

 

strSQL = "INSERT INTO tblHistoryItems " _

& "(InsertsReceived, " _

& "InsertsAndUpdatesSent, Conflicts, " _

& "ConnectionType, DataFlow, DeletesReceived, " _

& "DeletesSent, DesignChangesReceived, " _

& "DesignChangesSent, ErrorCode, Errors, " _

& "ErrorText, InitiatedDate, LastAction, " _

& "RemoteReplicaName, ReplicaName, Scheduled, " _

& "SynchStatus, TableModifications, " _

& "UpdatesReceived) " _

& "VALUES(" & hst.InsertsReceived & ", " _

& hst.InsertsAndUpdatesSent & ", " _

& hst.Conflicts _

& ", " & hst.ConnectionType & ", " & hst.DataFlow _

& ", " & hst.DeletesReceived & ", " _

& hst.DeletesSent _

& ", " & hst.DesignChangesReceived & ", " _

& hst.DesignChangesSent & ", " & hst.ErrorCode _

& ", " & hst.Errors & ", " & QT _

& hst.ErrorText & QT _

& ", #" & hst.InitiatedDate & "#, #" _

& hst.LastAction _

& "#, " & QT & hst.RemoteReplicaName & QT _

& ", " & QT _

& hst.ReplicaName & QT & ", " _

& hst.Scheduled & ", " _

& hst.SynchStatus & ", " _

& hst.TableModifications & ", " _

& hst.UpdatesReceived & ")"

 

Set db = CurrentDb

db.Execute strSQL

 

SyncMe = hst.Conflicts + hst.Errors

 

moSync.DatabaseName = ""

 

End Function

 
 
Synchronizer events and properties
Note that for certain actions, you need to set the Synchronizer's Running property to True. This attaches the Synchronizer object to the Microsoft Jet Synchronizer. However, if the Jet Synchronizer is already being run (either by another Synchronizer object or the Microsoft Replication Manager), then a runtime error will be generated if you try to set the Running property to True. When your Synchronizer object variable goes out of scope or is set to Nothing, it will detach from the Jet Synchronizer, but it won't shut down until you explicitly set the Running property to False. Make sure that you set the Running property to False before breaking the connection between the object and the object variable:
 

   moSync.Running = False

   Set moSync = Nothing

 
 
One very useful feature of the TSI Synchronizer is that it allows you to trap events when creating or synchronizing replicas. This allows you to create your own progress meters, which you can't do using the Replication Manager, VBA code, or the menus. For example, you can place the CreateNewReplica and SyncMe functions in a class module and use the WithEvents keyword in the class module's Declarations section:
 

Private WithEvents moSync As Synchronizer

 
 
This will allow you to sink the events provided by the TSI Synchronizer. For creating replicas, this includes the BeginMakeReplica, EndMakeReplica, MakeReplicaProgress, and MakeReplicaFailed events. If you implement the class module using Microsoft Access, you can use the SysCmd function to create a progress meter on the status bar, as shown in the following examples:
 

Private WithEvents moSync As Synchronizer

Private fStatusInitialized As Boolean

 

Private Sub moSync_BeginMakeReplica()

   Call SysCmd(acSysCmdSetStatus, _

              "Starting To Make Replica...")

End Sub

 

Private Sub moSync_MakeReplicaProgress _

 (UnitsDone As Long, UnitsTotal _

 As Long, Cancel As Boolean)

 

   If fStatusInitialized = False Then

       Call SysCmd(acSysCmdInitMeter, _

        "Making Replica...", UnitsTotal)

       fStatusInitialized = True

   End If

   Call SysCmd(acSysCmdUpdateMeter, UnitsDone)

End Sub

 

Private Sub moSync_EndMakeReplica()

  Call SysCmd(acSysCmdRemoveMeter)

  Call SysCmd(acSysCmdSetStatus, _

       "Make Replica has completed.")

  fStatusInitialized = False

End Sub

 

Private Sub moSync_MakeReplicaFailed()

   Call SysCmd(acSysCmdSetStatus, _

        "Make Replica failed.")

  fStatusInitialized = False

End Sub

 
 
The code for implementing a progress meter for synchronization is similar to that for creating replicas, since both the MakeReplica and the SynchDirect methods have Begin, End, Progress, and Failed events. The following code snippet implements a progress meter for synchronizing replicas:
 

Private Sub moSync_SynchProgress(UnitsDone As Long, _

UnitsTotal As Long, Cancel As Boolean)

 

   If fStatusInitialized = False Then

       Call SysCmd(acSysCmdInitMeter, _

        "Synchronizing...", UnitsTotal)

       fStatusInitialized = True

   End If

   Call SysCmd(acSysCmdUpdateMeter, UnitsDone)

End Sub

 
 
Miscellaneous Synchronizer actions
There are also several other chores that you can use the Synchronizer to perform. One of them is moving a replica from one location to another without changing its ReplicaID. If you attempt to copy a replica using Windows Explorer (or a DOS file copy action), the Jet database engine will automatically assign it a new ReplicaID to ensure that no two replicas in the replica set have the same unique identifier. The MoveReplica method will let you move a replica and optionally change its name without the ReplicaID changing.
 

' Move the replica without changing the ReplicaID

moSync.MoveReplica "c:\repl_1.mdb", _

 "c:\replicas\repl_1.mdb"

 

' move the replica, change the name but not ReplicaID

moSync.MoveReplica "c:\repl_1.mdb", _

 "c:\replicas\repl_2.mdb"

 
 
Using MoveReplica whenever you need to relocate a replica is a good way of ensuring that you don't have invalid replicas listed on either your Access menus or in the Replication Manager. However, if you do get into a situation where you have invalid replicas, you can delve into the ReplicaSet property of the Synchronizer object to painlessly get rid of them. ReplicaSet is a collection of Replica items and contains all of the replicas managed by the Microsoft Jet Synchronizer on a particular machine. You can use it to point to a collection of managed replicas for the Jet Synchronizer, or to the collection of known replicas associated with the DatabaseName property. The ReplicaSet property has Append and Remove methods, and Count and Item properties, which you can use to manage new replicas or stop managing old replicas.
 
Each Replica item in ManagedReplicas has the following properties:

Description
MachineName
ReplicaID
ReplicaName
UNCName
SynchronizerID

 
You can remove a Replica item from ManagedReplicas by passing its ReplicaID to the Remove method of the ReplicaSet, as shown in the following function:
 

Public Function ReplicaRemove _

(strDatabaseName As String, _

strDeleteDB As String)

   ' Remove a replica from the replica set

   Dim ReplSet As Replicas

   Dim ws As Workspace

   Dim db As Database

 

   If moSync Is Nothing Then

      Set moSync = New Synchronizer

   End If

 

   ' Get the database you want to remove

   Set ws = DBEngine.Workspaces(0)

   Set db = ws.OpenDatabase(strDeleteDB)

 

   ' Point to the ReplicaSet you want to remove

   ' it from

   moSync.DatabaseName = strDatabaseName

   Set ReplSet = moSync.ReplicaSet

 

   ' Remove it

   ReplSet.Remove db.ReplicaID

 

   moSync.DatabaseName = ""

End Function

 
 
One other task you used to need the Replication Manager for was setting the retention period on a replica by using the Synchronizer object's RetentionPeriod property. The default retention period is set automatically by Jet, and you can't change it from the Access replication menus or from code. If you create a replica using DAO or the Replication Manager, then the default retention period is set to 60 days. However, if you create the replica using Access menus or the Briefcase, then the default retention period is set to 1,000 days. This allows too much time to store history about schema and data changes and can bloat up your replicas. To customize the retention period, you must have the Design Master open, since it's read-only for other replicas, as shown in the following function:
 

Public Function SetRetention _

 (strDesignMaster As String, intDays As Integer)

   If moSync Is Nothing Then

     Set moSync = New Synchronizer

   End If

   moSync.DatabaseName = strDesignMaster

   moSync.RetentionPeriod = intDays

   moSync.DatabaseName = ""

End Function

 
 
Scheduling
Scheduling is probably best done using the Replication Manager, since it provides a graphical user interface for doing so. The TSI Synchronizer has a 7x96 Boolean matrix that stores a one-week period of "when to synchronize" in 15-minute intervals. For example, 1,1 is 12:00 a.m. Sunday; 1,2 is 12:15 a.m. Sunday; 2,1 is 12 a.m. Monday; and so forth throughout the week. A visual representation of this is what you get when you use the Replication Manager. There's no UI for this provided with the Synchronizer object, but you could conceivably write your own.
 
There are two methods of the Synchronizer object that you can use to set the schedule: SetFullSchedule and SetSimpleSchedule. The best way to use SetFullSchedule is to use the GetFullSchedule to retrieve the existing schedule, and then use that as a starting point. You can also create the schedule from scratch by using code such as the following:
 

Dim afName(0 to 6,0 to 95) As Boolean

 
 
However, if the bitmap that you create isn't defined correctly, a runtime error will occur when you try to set the schedule. Instead of dealing with the entire schedule, you might find that using the SetSimpleSchedule method is easier to use. You can cumulatively set schedule items or clean out the whole schedule, as needed. All the parameters are optional, with default values, so if you don't pass any arguments, it will just run every day at 3 a.m. SetSimpleSchedule uses the five optional parameters shown in Table 4.
 
Table 4. SetSimpleSchedule parameters.

Parameter

Meaning

ReplicaName

This is the name of the target replica (or its ReplicaID, or even its SynchronizerID) with which synchronization is to take place. If you omit this parameter, the local synchronization schedule will be set.

DaysToSync

This parameter is a bitmask of SynchDayEnum values. You can set it for a single day (for example, synchDayMonday), or you can use the bitwise Or operator to combine two or more days (for example, synchDayTuesday Or synchDayThursday). If you don't specify the parameter, the default is SyncAllEveryday.

StartTime

This is used to specify the first time in each selected day to do a synchronization. If a value isn't provided, the default is #3:00 AM#.

TimesPerDay

This is used to specify the number of times between StartTime and Midnight of each selected day that a synchronization should take place. By default, this value is 1, which means that only the time specified in StartTime will be used.

ClearOldSchedule

This Boolean parameter allows you to choose whether you would like to clear out the old schedule values or combine the existing schedule with your new values.

 
 
This easy-to-call method can be very powerful, especially if you call it multiple times with ClearOldSchedule set to False. This will allow you to set up some very sophisticated schedules without having to deal with the complicated scheduling bitmap of SetFullSchedule. The following function makes use of SetSimpleSchedule:
 

Public Function TestSchedule( _

Optional strReplicaName As String, _

Optional lngDaystoSync As Long = synchDayEveryday, _

Optional datTime As Date, _

Optional intTimesPerday As Integer = 1, _

Optional fClearOld As Boolean)

 

   If moSync Is Nothing Then

     Set moSync = New Synchronizer

   End If

 

   moSync.DatabaseName = strReplicaName

   moSync.SetSimpleSchedule strReplicaName, _

      lngDaystoSync, datTime, intTimesPerday, fClearOld

   moSync.DatabaseName = ""

End Function

 
 
Hidden stuff
If you right-click in the Object Browser and choose "Show Hidden Members" for the object, you'll see that there are quite a few hidden methods there. Some of them involve replication features, such as the following:

There's a PopulatePartial method. There are no status events with it (which is unfortunate, because they'd be very useful!), but there is one special parameter that DAO doesn't expose. In a partial replica, a user might have entered data that doesn't meet the filter, such as adding a customer from New York City in a partial replica that filters out all customers who don't live in Syracuse. In order to make sure that data isn't lost when calling PopulatePartial to clean up the data, a direct synchronization is done first. This synchronization is performed even if the filters aren't in synch (in which case Jet won't allow you to do a regular synchronization). In DAO, however, you can't make the PopulatePartial fail if this synchronization fails. The TSI Synchronizer's PopulatePartial has an optional FailOnPushSynchError argument, which will halt the PopulatePartial so that you don't lose data that couldn't be propagated to the full replica.
There are Register and Unregister methods, which will register and unregister the Jet Synchronizer. Register will add some default properties and a default "internal" schedule, while Unregister will clean up and remove those Registry entries. It's a good idea to call the Unregister method first when you're removing the Synchronizer.
The SynchronizerID property will return the GUID that identifies the Jet synchronizer on this machine.

 
There are also a few hidden features that have nothing to do with replication. However, they expose functionality that isn't exposed anywhere else:

The TSI Synchronizer CompactDatabase method doesn't have some of the functionality of the DAO CompactDatabase method, such as being able to change the sort order of the database or handle database passwords. It does, however, do one thing that users have been asking for since Access 2: It provides status events that you can use for a status bar or progress meter. These events are BeginCompactRepair, EndCompactRepair, CompactRepairProgress, and CompactRepairFailed.
The RepairDatabase method works the same way as the CompactDatabase method does (it even shares the same events). Note that if you have a newer version of Jet that contains the enhanced repair functionality that's combined with Compact, then this method will fail with an error (synchErrRepairDisabledWithNewJet).

 
These are just a few of the hidden methods and properties embedded in the TSI Synchronizer. You can explore the rest by taking a look in the Object Browser and seeing what you can find!
 
Conclusion
We've only scratched the surface of what the TSI Synchronizer can do for you. You can also use it to set up indirect and Internet synchronizations, work with secured databases, and perform many other tasks. The TSI Synchronizer wraps up all of the functionality you need to create robust and powerful replicated database solutions.
 
Read about the download TSISYNC.ZIP on this page