Creating a Partial Replica

A partial replica is a replica that does not contain all the data of the original object. This type of replica is useful for those who only need to see or work with certain data. A partial replica is created by setting an expression-based or relationship-based filter on tables or queries. A partial replica contains all the tables that exist in the full replica; however, the user only sees the data as allowed by the specified filter. Therefore, some of the tables in a partial replica are empty. Because partial replicas have fewer records, they can synchronize quicker than full replicas.

You can create a partial replica by using the CreateReplica method and specifying two required arguments (the partial replica's name and description) and an enumeration constant of jrRepTypePartial for the optional argument. For instance, in Hands-On 18-3 you use the following statement to create a partial replica to display records for only Spanish customers:

repDesignMaster.CreateReplica repPartialName, _ "Partial Replica (Spanish Customers)", _ jrRepTypePartial

Part II

The above statement creates a partial replica. Next, you must specify which records the partial replica will contain by defining filters. Each replica can have one or more filters.

There are two types of filters in JRO:

An expression-based filter is a criterion that a record in the partial replica table must satisfy in order to be replicated from the full replica. This criterion is like the SQL WHERE clause without the WHERE keyword. For example, the procedure that follows uses a criterion to allow access only to customer records from the specified country: "[Country] ='Spain'"

The second type of filter, a relationship-based filter, restricts the records based on relationships between tables. In Hands-On 18-3 below, only information related to Spanish customers will be accessible to the user.

After setting the filters, use the PopulatePartial method to add records to a partial replica based on the specified filter. For instance:

repPartial.PopulatePartial repMasterName

In the statement above, repPartial is an object variable that points to the partial replica. repMasterName is a string variable that denotes the path and filename for the full replica set.

The PopulatePartial method will first clear all records in the partial replica, then repopulate the replica based on the specified filters. This type of operation requires that the partial replica be opened with exclusive access.

©Hands-On 18-3: Creating a Partial Replica

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the module's Code window, enter the Make_PartialReplica procedure as shown below.

Sub Make_PartialReplica(FilterOption As Integer) Dim repDesignMaster As New JRO.Replica Dim repPartial As New JRO.Replica Dim flt As JRO.Filter Dim repMasterName As String Dim repPartialName As String Dim strFilterType As String

On Error GoTo ErrorHandle repMasterName = CurrentProject.Path & "\DM_Northwind.mdb" repPartialName = CurrentProject.Path & "\Spanish.mdb"

' Create partial replica repDesignMaster.ActiveConnection = repMasterName repDesignMaster.CreateReplica repPartialName, _ "Partial Replica (Spanish Customers)", _ jrRepTypePartial

Creating and Manipulating Databases with ADO

Set repDesignMaster = Nothing

' open an exclusive connection to the partial replica repPartial.ActiveConnection = "Provider=Microsoft.Jet.0LEDB.4.0;" &_ "Data Source= " & repPartialName & ";Mode=Share Exclusive"

If Filter-Option = 1 Then

' Create an expression-based filter in the partial replica repPartial.Filters.Append "Customers", jrFilterTypeTable, "[Country] ='Spain'"

Elself FilterOption = 2 Then

' Create a relationship-based filter in the partial replica repPartial.Filters.Append "Customers", jrFilterTypeTable, "[Country] ='Spain'"

repPartial.Filters.Append "Orders", jrFilterTypeRelationship, "CustomersOrders"

End If

' Populate the partial replica based on the specified filter repPartial.PopulatePartial repMasterName

MsgBox "Partial replica named " & repPartialName & vbCrLf _

& "was created based on the selected filter type." & vbCrLf _ & "Please view filter information in the Immediate window."

' Print filter information to the Immediate window For Each flt In repPartial.Filters

If flt.FilterType = jrFilterTypeTable Then strFilterType = "Table Filter"

Else strFilterType = "Relationship Filter" End If

Debug.Print "Table Name: " & flt.TableName & vbCr _ ; vbTab & "Filter Type: " & strFilterType & vbCr _ ; vbTab & "Filter Criteria: " & flt.FilterCriteria

Next

ExitHere:

Set repPartial = Nothing Exit Sub ErrorHandle:

If Err.Number = -2147217897 Then Kill repPartialName Resume

Else

MsgBox Err.Number & ": " & Err.Description Resume ExitHere End If End Sub

3. Execute the Make_PartialReplica procedure by typing the following statement in the Immediate window and pressing Enter:

Make_PartialReplica 1

Part II

The above statement specifies that the Make_PartialReplica procedure should use the expression-based filter (1) when repopulating the partial replica. The filter information is printed to the Immediate window:

Table Name: MSysCmdbars

Filter Type: Table Filter Filter Criteria: TRUE Table Name: MSysIMEXColumns Filter Type: Table Filter Filter Criteria: TRUE Table Name: MSysIMEXSpecs Filter Type: Table Filter Filter Criteria: TRUE Table Name: MSysAccessObjects Filter Type: Table Filter Filter Criteria: TRUE Table Name: Customers

Filter Type: Table Filter Filter Criteria: [Country] ='Spain'

4. Execute the Make_PartialReplica procedure again by typing the following statement in the Immediate window and pressing Enter:

Make_PartialReplica 2

The above statement specifies that the Make_PartialReplica procedure should use the relationship-based filter (2) when repopulating the partial replica.

0 0

Post a comment