Indirect Replication of Access 2003 databases

One of the most useful features in the developer editions of Microsoft Access up to version 2003 was the inclusion of a utility called Replication Manager. As the name suggests, this let you set up and manage replication between two or more copies of an Access database. You could manage replication across networks, using the Internet, or using indirect replication. Unfortunately, Microsoft decided to drop support from the feature in Access 2003. If you want to use replication with Access, your easiest solution is to stay with Access XP or 2000, or at least to keep the replication facilities from those versions. However, if you have to move to Office 2003, and to Access 2003, you’ll need to find an alternative solution if you want to use indirect or Internet replication.

The replication features of Access are rather better than any rival desktop database, and are in fact a lot better than many server databases. The way it works is that you make one or more copies of your database. These copies can be used independently at different locations, and the individual replicas can then be synchronised with the ‘master’ copy of the database. When you replicate the database, you’re shown tables of conflicts where the same record has been changed in more than one database. You are given the choice of keeping the original data or the changed data, so one set of changes will be lost.

You can still use replication in Access 2003 without the Replication Manager, but the problem you will have is that the whole replica databases will have to be accessible to the machine containing the master database. This isn’t too much of a problem if you’re running the replication on a network, but it is a problem for the more traditional use of replicated databases, where the copies are located at different branch offices, say. In this case, the whole database will have to be transferred to and fro for replication – not an ideal situation by any means.

You can show the fields added when you set a database as replicable by setting Tools, Options, View, and making System Objects and Hidden Objects visible

One option is to write your own version of the replication manager, or at least the elements of it that take care of transferring the ‘changed’ portions of a database via the Internet, or still better, via a VPN. In order to do this, you’ll need to download some of the underpinnings that the replication manager relied on – the actual synchroniser that is used to transport the changed portions of the database too and fro. Fortunately, the necessary files can be downloaded from the Microsoft website.

If you download the file Jet40repl.exe from this location, and run it, you’ll get the files for indirect and internet replication that Access (or the Jet database engine if you’re working outside Access) needs for synchronising files. The files are installed in the directory:

C:\Program Files\Common Files\
	Microsoft Shared\Replication
	Manager 4.0

…and the appropriate file will need to be run when you begin synchronising databases. Before we get to that stage, though, we need to work out how to manage the replication.

The dropbox folder complete with message file

Replicated database structures

When you change a database to a replicated database, it has extra elements added to its structure, and you can make use of these to manage the replication. Some fields are added to the ‘normal’ data tables. Extra system tables are also added. All Access databases have system tables, but they’re usually hidden because you don’t need to do anything to them – they are what Access uses to manage the data. Replicated databases have extra system tables that record the history of exchanges between replicas, the location of other replicas in the replica set, and other information required by the system.

There are three fields that are added to your data tables. A GUID (globally unique identifier) field is added to the structure. GUIDs are guaranteed to be unique, so this takes care of the chance that two users add new records to different replicas. This field has a default name of s_GUID. If you already have an autonumber field in a table to ensure unique records, it will be used instead of a new field, and its type will be changed to a GUID. A Generation field is also added. This is a field that is used to work out which records have been changed when you ask to synchronise replicas. The generation field is used both as a counter for how many times the record has been changed, and as a flag to show that the record has been changed since the last synchronisation.

The final field that is added is a lineage field. This is a field that tracks the version and replica number of each record in each table. This field is used to ensure that when a change has been processed, it isn’t sent again for reprocessing.

You will only see the lineage and generation fields if you set Access to display system objects and hidden objects. However, you can make use of the fields in your applications.

For example, to check the generation and lineage for the first record in the employees table you could say:

Sub checklin()
	Dim wk As Workspace
	Dim db As Database
	Dim rs As Recordset
	Set wk = CreateWorkspace( _
		"", "admin", "", dbUseJet)
	Set db = wk.OpenDatabase( _
		"c:\data\expenses.mdb")
	Set rs = db.OpenRecordset( _
		"select * from employees")
	With rs
	 	.MoveFirst
		MsgBox (rs!s_generation)
		MsgBox(rs!s_lineage)
	End With
End Sub

On the other hand, the only time you’re really likely to need to see what’s going on is while you’re creating the application, and so long as you’ve set the view to show hidden objects, you can simply view the values in situ in the tables.

The TSI Synchonizer object has several hidden methods that may be useful – right-click on the object to see them

Indirect or Internet synchronisation?

As already mentioned, Jet replication provides both direct and indirect ways to synchronise your databases. Direct synchronisation can be handled using the features that remain within Access, so the one that we’re concerned with is indirect. The way indirect synchronisation works is that updates in a local replica are collected into a message file, which is stored in a dropbox folder. When a remote replica wants to synchronise with the local replica, it looks in the dropbox folder, reads the message file and applies the changes to its local data. Access can carry out the indirect system either over a network or file system or over the Internet. In the ‘old’ days, the indirect method essentially required you to have an SMB network connection, which meant your other database had to be on your LAN or WAN, or you needed to use dialup networking. Fortunately, VPNs are now much easier to set up and manage, so you can carry out your indirect replication over the Internet via your VPN.

The reason why this is a preferable solution to the more obvious Internet replication is that in order to carry out Internet replication, you have to use IIS, and in particular, the IIS FTP service. What’s more, you have to run IIS on all the PCs that you’re going to synchronise to and from. This obviously adds overheads in terms of the load on the system, but more importantly, do you really want your machines running IIS and its FTP service? It’s far more secure to sit within your VPN.

What documentation exists (and it is woefully out of date) tells you that you need Replication Manager to set up indirect synchronisation. This isn’t quite true; you need the synchronisation files you downloaded earlier, and some way to manage the synchronisation schedule, but the Replication Manager isn’t required. It is a lot easier, though, so if you have a version from an earlier release of Office, it’s worth hanging onto and using.

If you don’t have any way of getting Replication Manager, you need some way to specify settings such as the synchronisation schedule to the synchroniser. One option to do this is to make use of JRO – Jet and Replication Objects. This was a rather nice library that gave you programmatic access to the replication and synchronisation facilities in Access. However, Microsoft has deprecated JRO, and will withdraw it completely in the next version of Office. The minimal examples and help that are available all assume that you have the Replication Manager available, and as in many of Microsoft’s products, give you the feeling that you’ve been abandoned.

By far the easiest way to programmatically control replication in Access is via a third party utility called the TSI Synchronizer that you can download from Michael Kaplan’s trigeminal site.

The TSI Synchronizer is the missing element that means Indirect Synchronisation is still possible even though Microsoft has left us in the lurch, as it gives you the means to specify in your applications the necessary settings for indirect synchronisation.

To make indirect replication work, you need to install Microsoft’s synchronisation files that you downloaded earlier, and the TSI Synchronizer, at both the local and remote sites. You also need to create shared folders at both sites. You can then use the TSI Synchroniser to make the shared folder the dropbox folder for each synchroniser.

You also need to add a key to your system registry so that TSI Synchronizer knows where to find the files used for synchronisation. The key you need to add is:

HKey_Local_Machine\SOFTWARE\Microsoft\
	Jet\4.0\Replication Manager

Under this key, place a string value “SynchronizerPath” and set its value to be the full path and filename to mstran40.exe.

You’re then at the stage of needing to write some code.

In order to set up an indirect synchronisation, you need at least two databases that are replicas in the same set. In Access, database replicas are managed as replica sets, where a replica set consists of the Design Master and all replicas that share the same database design and unique replica set identifier. You can then synchronise members of the set by exchanging all updated records and objects in each member. You can create a replica of a database using the TSI Synchronizer using code along the lines of:

Sub makerep()
Dim sync As Synchronizer
Dim reps As Replicas
	Set sync = New Synchronizer
	Sync.Running=True
	sync.DatabaseName = _
		"C:\data\expenses.mdb"
	Set reps = sync.ReplicaSet
	sync.MakeReplica _
		"c:\data\exprep.mdb"
	Set sync=Nothing
End Sub

The synchronizer referred to in the code is the Microsoft Jet synchronizer, which for indirect replication is the file mstran40.exe. The statement:

Sync.Running=True

…makes sure that the TSI Synchronizer object is attached to the Microsoft Jet Synchronizer on the machine. You have to set this property to True for many of the TSI Synchronizer actions to work.

When your Synchronizer object variable goes out of scope or is set to Nothing, it will detach itself from the Synchronizer on the machine but the Jet Synchronizer will remain running. If you want to stop the Jet Synchronizer running, you can do so by setting the sync’s Running property to False.

The code above fairly obviously places the replica in the same folder as the original database – not very useful for replication. Resist the temptation to move the replica using Windows. Behind the scenes, replicas keep track of the other members of the replica set by having a list of the ReplicaIDs, a unique identifier for each member of the set. If you move a replica using Windows or Explorer, Jet will give the replica a new ReplicaID, and you’ll end up with a replica set where some members no longer exist.

Instead, you need to use TSI Synchronizer’s MoveReplica method. This maintains the correct IDs among the other set members:

Sub makerep()
Dim sync As Synchronizer
Dim reps As Replicas
	Set sync = New Synchronizer
	Sync.Running = True
	Sync.DatabaseName = _
		"C:\data\expenses.mdb"
	Set reps = sync.ReplicaSet
	Sync.MoveReplica _
		"c:\data\exprep.mdb", _
		"z:\exprep.mdb"
	set Sync = Nothing
End Sub

This moves the replica to drive Z, actually a folder on a networked drive elsewhere on the VPN. You can change the name as well of the location of a replica using MoveReplica.

So far, we’ve simply set up replicas of the database, and if drive Z is visible all the time, those replicas could be managed using direct replication. The next step is to move on to indirect replication.

As mentioned earlier, indirect replication works by using shared folders called dropboxes. These are folders that are visible across the network or VPN. When you ask to synchronise a database, the updates from the local replica are collected into a message file, which is then stored in your local dropbox folder. When a remote replica wants to synchronise with the local replica, it looks in the dropbox folder, reads the message file and applies the changes to its local data.

Depending on the way your network and security is set up you may well need to specify the SystemDB, UID and Password properties for your replica database. You’ll also need to set the name and location of the message dropbox:

sync.SystemDB = _
	"c:\windows\system32\system.mdw"
sync.UID = "kay"
sync.password = "password"

If you don’t set these properties, UID will be set to Admin and the password to a zero length string.

To set the dropbox details, at the very least you need the name and path of the dropbox the Microsoft Jet Synchronizer will use for indirect synchronizations in a format that Jet or Access can understand. This dropbox is a local shared folder that is visible as a top-level share, and it will be used to hold the message files that the other synchronizer uses to help send information on changes to make. The Synchronizer on this machine then applies the changes to the replica it is managing. The details are set using the TSI Synchronizer’s IndirectDropbox property.

If the dropbox folder needs a password to access it, you also need to specify the IndirectDropboxPwd.

Set sync = New Synchronizer
sync.Running = True
sync.IndirectDropbox = _
	"\\vsj1\dropbox"
sync.IndirectPassword="password"

Now comes the task of actually synchronising the databases. Obviously, you’ll only see results for this if you’ve made changes to the data in one or more databases in the set.

Dim sync As Synchronizer
Dim reps As Replicas
Dim rep As Replica
Set sync = New Synchronizer
Set reps = sync.ReplicaSet
sync.Running = True
sync.SystemDB = _
	 "c:\windows\system32\system.mdw"
sync.IndirectDropbox = "c:\dropbox"
sync.IndirectDropboxPwd = "password"
sync.DatabaseName = _
	"C:\data\expenses.mdb"
Set reps = sync.ReplicaSet
For Each rep In reps
	If rep.ReplicaID <> _
		sync.ReplicaID Then
		sync.SynchIndirect _
			rep.SynchronizerID
	End If
Next rep

You should find that two files are created in the dropbox. The first is a file called wingman.sgn. This is a file used by Access and Jet to ensure the correct synchroniser for the folder is being used. It’s actually a text file, and if you open it in NotePad you’ll see it contains the name of the managing synchroniser for the dropbox folder.

Once you’ve carried out a synchronisation, you should find .msg files in the dropboxes. It can be worrying to begin with because Synchronizer A on machine 1 puts files in Synchronizer B’s dropbox on machine 2.

Once synchronisation has occurred, the user will be shown a dialog alerting them to the fact

When to synchronise

You should now be able to run a procedure that will synchronise two or more databases in a replica set, and if you attach that procedure to a command button, you will give your database users the option of synchronising when they feel it’s appropriate. This is fine for users who understand what’s happening, and in most cases you will need to have users who understand synchronisation because they’ll need to look at the conflicts tables so they can work out what data should be kept. However, in some circumstances you will want to go further and set the synchronisation to happen on a schedule, so that the databases are synchronised once a day, hour, or whatever.

This requires you to set up a synchronisation schedule, which used to be reasonably easy in Replication Manager, but is quite tricky without it. Behind the scenes, the way Replication Manager handles replications is by an array showing the times in a format that is day, timeslot. The timeslots are arranged in 15 minute intervals, so 1,1 is Sunday, 12 am. 1,2 is Sunday, 12:15 am, and 3,1 is Tuesday, 12 am. This is easy enough to understand, but a pain to do the counting, and can be somewhat error-prone. Replication Manager gives you a graphical interface to overcome this; TSI Synchronizer leaves you on your own.

You can set the schedule for the Sychronizer in two main ways – either by setting a full schedule or a simple schedule. You can also read in the existing schedule.

To set the full schedule, you use SetFullSchedule. For example, the following code sets all the schedule entries to false (so no synchronisation takes place), then sets a schedule of once an hour (one in every four of the 15 minute slots) for the first replica in the set:

Dim sched(0 To 6, 0 To 95) As Boolean

For a = 0 To 6
	For b = 0 To 95
		sched(a, b) = False
	Next
Next
sync.SetFullSchedule _
	reps(1).ReplicaName, sched

For a = 0 To 6
	For b = 0 To 95 Step 4
		sched(a, b) = True
	Next
Next
sync.SetFullSchedule _
	reps(1).ReplicaName, sched

Alternatively, you can use the SetSimpleSchedule method to specify particular times for the schedule to occur. SetSimpleSchedule takes five parameters – ReplicaName, DaysToSync, StartTime, TimesPerDay, and ClearOldSchedule. DaysToSync can be set for a particular day (syncDayTuesday), or you can specify multiple days using the bitwise Or operator (synchDayTuesday Or synchDayThursday). If you don’t specify the day, it assumes a default of all days.

If they choose to resolve the conflicts, they’ll be shown the items one by one so they can choose which version should ‘win’

Conclusion

Controlling synchronisation programmatically ought to be easy, and the main reason it isn’t lies in the fact that there’s nowhere you can really go to get help. There is a reasonably good newsgroup that can help if you get stuck at Microsoft.public.access.replication – there are three or four messages a week and a couple of people posting who know what they’re talking about. The main TSI site (www.trigeminal.com) also has some useful information, though it is very short on useful samples. Other than that and archives on Google Groups, you are pretty much on your own.

The good news is that indirect replication IS still possible; it does work, and when it’s working, you can solve problems that seemed insurmountable without it.

---

Kay Ewbank, Editor of Server Management magazine, is a highly experienced database analyst who has followed the development of database technology from dbase through to today’s SQL servers.