CONVERTING A DATABASE TO A REPLICA

Calling the MakeReplicable method on a database automatically converts the database to a replicable format. A new feature in Access 2000 is the ability to track data changes at the column (cell) level as well as the row (record) level. A column is the most basic unit of information recognized in replication when the ColumnLevelTracking, sometimes referred to as CLT, is set on a table. The ColumnLevelTracking property is a custom property, which exists both on the database and on each individual table. ColumnLevelTracking is the default on each table when the ColumnLevelTracking property is set to TRUE on the database. The database CLT property can be set only when making the database replicable. When a table's custom ColumnLevelTracking property is set to TRUE, nonconflicting fields are merged when changes are transmitted during synchronization. When ColumnLevelTracking is set to FALSE or equally when RowLevelTracking is set for the table in the table's property sheet in Access, if any field in a row (record) is modified, the whole record is marked as changed. Therefore, the whole record is updated when changes are transmitted during synchronization and individual fields aren't merged.

A ColumnLevelTracked table significantly reduces the potential for conflicts and simplifies the maintenance of replicated databases, if different users frequently edit the same data. ColumnLevelTracking creates a small performance hit, due to the added system columns and compare logic, and therefore shouldn't be used in cases where users are doing large updates to isolated tables—that is, tables other users wouldn't be updating.

You can find the example of making a database replicable and setting the default tracking in the global module Replication Routines in the Chap23.mdb database, which is on the accompanying CD-ROM in the ExamplesChap23 folder. Also, the sample Listing 23.1 below shows how to make a database replicable.

Listing 23.1. Chap23.mdb: Converting a Database to a Replica

Sub MakeDatabaseReplicable(strTargetDB As String, _
   boolCLT As Boolean)

     Dim TargetDB As New JRO.Replica

     'Error checking code here, making sure target exists...

     'Database is opened in exclusive mode by default.
     TargetDB.MakeReplicable strTargetDB, boolCLT

     Set TargetDB = Nothing

     'Confirm when finished
     MsgBox (strTargetDB & " is now a replicable database")

End Sub

The MakeDatabaseReplicable subroutine is used on a form named Make Database Replicable. The form has the user input a database to make replicable in the txtTargetDB text box and the tracking default by checking the chkTrackingLevel check box, where True is ColumnLevel Tracking. The user then clicks the cmdMakeDatabaseReplicable command button, which has the code shown in Listing 23.2 on the OnClick event.

Listing 23.2. Chap23.mdb: Input Form for Making a Database Replicable

Private Sub cmdMakeDatabaseReplicable_Click()

Dim strTargetDB As String
Dim boolTrackingLevel As Boolean

'Place error checking code here to ensure that database exists.

strTargetDB = Me!txtTargetDB
boolTrackingLevel = Me!chkTrackingLevel

'Make target database replicable
MakeDatabaseReplicable strTargetDB, boolTrackingLevel

End Sub

The database is now “replicable,” allowing you to create additional replicas and synchronize between replicas.

In Listing 23.1, the database was converted into a replicable format. The default tracking of all existing tables and newly created tables is either ColumnLevelTracking or RowLevelTracking, depending on the value of the variable boolTrackingLevel. Of course, replication is useful only when there's more than one copy of the database, so the next logical step is to create an additional replica. You do this by creating a new replica from a database that's already replicable.

To create an additional replica, you use the CreateReplica method, which is applied to an existing replicated database object and creates a new replica with the filename provided. You can optionally provide a user-friendly description (for example, Jim's replica of Customer Accounts), define ReplicaTypeEnum, define VisibilityEnum, set the replica Priority, and define the UpdatabilityEnum replication attributes. Each parameter including the Enum values are defined below. (Access 2000 introduces the new Visibility and Priority features to make replication more powerful.) This is the syntax:

						rep.CreateReplica strTargetDB,
						strDescription,
						ReplicaType, _
   Visibility,
						Priority,
						Updatability
					

The CreateReplica method has the following parameters:

• rep The source database.

• strTargetDB A String value specifying the name and path of the replica to be created.

• strDescription A String value describing the replica to be created.

• ReplicaType An optional Enum value indicating the type of replica to be created. The following constants are valid values for ReplicaType:

  Constant	Description
  jrRepTypeFull	The replica is a full replica (the default).
  jrRepTypePartial	The replica is a partial replica.

  
  

• Visibility An optional Enum value indicating the replica's visibility. Replica visibility is described in full length later in the “Replica Visibility” section. The following constants are valid values for Visibility:

  Constant	Description
  jrRepVisibilityGlobal	The replica is global (the default).
  jrRepVisibilityLocal	The replica is local.
  jrRepVisibilityAnon	The replica is anonymous.

  
  

• Priority An optional Long value indicating the priority of the replica for use during conflict resolution. The default value is –1, which indicates that the database should determine the default value. For global replicas, the default priority is 90 percent of the parent replica's priority.

  Also, the valid values for a global replica can be further restricted. If the user is the database administrator, the entire range is valid. Otherwise, the maximum value for priority is 90 percent of the parent replica's priority. For local and anonymous replicas, the value will always be 0 and can't be changed. This value is forced with the creation of the replica and any other value is ignored.

• Updatability An optional Enum value indicating the type of updates allowed. The following constants are valid values for Updatability:

  Constant	Description
  jrRepUpdFull	The replica can be updated (the default).
  jrRepUpdReadOnly	All replicable objects are read-only.

  
  

By using the Create Additional Replica form in the Chap23.mdb database, the example in Listing 23.3, attached to the OnClick event of the cmdCreateAdditionalReplica command button, takes the filename of an existing replicable database, txtSourceReplica, and creates the additional replica with the filename passed in the string txtAdditionalReplica. This form also takes the user's values for Visibility, Priority, and Updatability. In Listing 23.3, the new replica is given the description Replica of the source.

Listing 23.3. Chap23.mdb: Creating Additional Replica

Private Sub cmdCreateAdditionalReplica_Click()

    Dim rep As New JRO.Replica
    Dim strSourceReplica As String
    Dim strNewReplica As String
    Dim eVisibility As VisibilityEnum
    Dim lVisibility As Long
    Dim lPriority As Long
    Dim eUpdateability As UpdatabilityEnum
    Dim lUpdateability As Long

    'Place your error checking here to ensure that your source DB exists.

    strSourceReplica = Me!txtSourceReplica
    strNewReplica = Me!txtAdditionalReplica
    lPriority = Me!txtPriority
    lVisibility = Me!frVisibility
    lUpdateability = Me!optReadOnly

    Select Case lVisibility
       Case 1
           eVisibility = jrRepVisibilityGlobal
       Case 2
           eVisibility = jrRepVisibilityLocal
       Case 3
           eVisibility = jrRepVisibilityAnon
    End Select

    Select Case lUpdateability
        Case -1
           eUpdateability = jrRepUpdReadOnly
        Case 0
           eUpdateability = jrRepUpdFull
    End Select

    ' Create an Active Connection to the source replica
    rep.ActiveConnection = strSourceReplica

    rep.CreateReplica strNewReplica, "Replica of " & strSourceReplica, _
       jrRepTypeFull, eVisibility, lPriority, eUpdateability

    Set rep = Nothing

    MsgBox "Your Replica has been created."

End Sub