Have a request for a topic in Code of the Week? Email us at request@codeoftheweek.com
This issue discusses a feature of Microsoft Access databases called replication. The complete source code appears near the end of this issue. Be sure not to miss it!
Replication allows the programmer to create a database that can be replicated (or copied) to multiple servers or locations and still keep the databases synchronized. Using this feature can increase performance in a remote site while still having a "almost real-time" database. We would like to give a little background on replication in case some of our members have never used or heard of it.
The replication feature of Access databases is quite powerful. The way Access implements its version of replication is to create a single database called the design master (use the MakeDesignMaster method to accomplish this task). This database is where you should make all your design changes (such as the addition of fields or indexes). From the design master you can create any number of replicas. Access supports full replicas (complete copy of the design master) and partial replicas (a subset of the data stored in the design master).
We will only discuss full replicas in this issue. In the design master you designate which tables you would like to be replicated using the MakeTableReplicable method in the cJetReplication class. Once you have made the tables you want replicable you can use the MakeReplica method to create the replica. Using MakeReplica will create a replicated database that you can access and update. Periodically the Synchronize method needs to be called to make updates to design master available to the replica and vice versa.
How often you perform the synchronize will depend on how "current" you require your database to be. Many companies we work with perform nightly synchronizations. One of the companies we work with perform partial replica updates over a dial-up connection to keep their field force laptops up-to-date. If you have an application that a partial replica would be useful for please contact us at partialreplica@codeoftheweek.com
The cJetReplication class makes replication a little easier to manage. It provides several properties and methods that allow you to easily add replication to your application.
Be sure to check out the Microsoft Access 97 Replication Site Tree at http://support.microsoft.com/support/access/content/repl/replication.asp because it has lots of great information about replication.
If you have any questions about using this class, let us know at questions@codeoftheweek.com
Public TableName As String
This is the table name that is used by the MakeTableReplicable and MakeTableLocal routines. It is not needed for any other methods.
Public ExclusiveMode As Boolean
When this routine is True the database specified by the DatabaseName property will be opened exclusively. The default is False which opens the database in a shared mode.
Public ReplicaName As String
Name of the replica to create (MakeReplica) or synchronize.
Public Property Let DatabaseName(sDatabaseName As String)
The database name to work with. You would normally set this parameter to your design master database name. You should specify the full path and filename.
Public Property Get DatabaseName() As String
Retrieve the name of the database that is currently opened by this class.
Public Sub OpenDatabase()
Allows you to open the database manually. This should not normally need to be called since setting the DatabaseName property will automatically open the database.
Public Sub CloseDatabase()
Allows you to close the database manually. This should not normally be necessary since terminating the class object will close the database.
Public Sub MakeDesignMaster()
Will set the database currently specified by DatabaseName to become a design master. Only one design master should exist or you will have synchronization problems. The database will need to be opened exclusively to perform this operation. It will automatically attempt to open the database exclusively and will raise an error if it fails.
Public Sub Synchronize()
Synchronizes the design master (DatabaseName) with the database specified in ReplicaName.
Public Sub MakeTableReplicable()
Sets the table specified in TableName to be replicable. The next time you perform a synchronization with a replica this table will be replicated.
Public Sub MakeTableLocal()
Sets the table specified in TableName to be local or non-replicable.
Public Sub MakeFullReplica(Optional sReplicaDescription As String = "")
Creates the replica specified by ReplicaName based on the database specified by DatabaseName. After this method completes you will have a completely replicated database. Use Synchronize as needed to keep the replica in sync.
Public Sub MakeFullReadOnlyReplica(Optional sReplicaDescription As String = "")
Same as MakeFullReplica except the replica will be read only.
See specific details for each property and method.
Below is an example of how this class works. It assumes a database called mail.mdb exists in a subdirectory called designmaster. The first thing this routine does it to make the mail.mdb database a design master. It then makes the Log table a replicable table. Then it creates a replica called mail-replica.mdb in the replica directory.
Dim oJetRep As New cJetReplication
On Error GoTo Handler
oJetRep.DatabaseName = App.Path & "\designmaster\mail.mdb"
oJetRep.MakeDesignMaster
oJetRep.TableName = "Log"
oJetRep.MakeTableReplicable
oJetRep.ReplicaName = App.Path & "\replica\mail-replica.mdb"
oJetRep.MakeFullReplica "this is a replica"
' Use the synchronize method at a later time to keep both databases in sync.
'oJetRep.Synchronize
Exit Sub
Handler:
MsgBox Err.Description & " occurred in " & Err.Source
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0089.html