
'----------------------------------------------------------------
' Hands-On 18-1
'----------------------------------------------------------------

Sub Create_DesignMaster(dbName As String, strPath As String)
    Dim repDesignMaster As New JRO.Replica
    Dim fso As Scripting.FileSystemObject
    Dim strDb As String

    ' create a new instance of the FileSystemObject
    Set fso = New FileSystemObject

    ' store a file name for the Design Master in a variable
    strDb = strPath & "DM_" & dbName

    ' create a copy of the sample Northwind database
    If Not fso.FileExists(strDb) Then
        fso.CopyFile strPath & dbName, strDb
    Else
        MsgBox "The " & strDb & " database file already exists."
        Exit Sub
    End If

    ' make the database replicable
    repDesignMaster.MakeReplicable strDb, True
    MsgBox "Your Design Master was successfully created." _
                , , "DM_Northwind"

    Set repDesignMaster = Nothing
    Set fso = Nothing
End Sub


'----------------------------------------------------------------
' Hands-On 18-2
'----------------------------------------------------------------

Sub Make_FullReplica(DesignMasterName As String, _
             NewRepName As String)
    Dim repDesignMaster As New JRO.Replica

    On Error GoTo ErrorHandler

    With repDesignMaster
        .ActiveConnection = DesignMasterName
        .CreateReplica CurrentProject.Path & "\" & NewRepName, _
            "Replica of " & DesignMasterName
    End With

    MsgBox "Full replica named " & NewRepName & " was created."

ExitHere:
    Set repDesignMaster = Nothing
    Exit Sub
ErrorHandler:
    If Err.Number = -2147217897 Then
        Kill CurrentProject.Path & "\" & NewRepName
        Resume
    Else
        MsgBox Err.Number & ": " & Err.Description
        Resume ExitHere
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 18-3
'----------------------------------------------------------------


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 ErrorHandler

    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

    Set repDesignMaster = Nothing

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

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

    ElseIf 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
ErrorHandler:
    If Err.Number = -2147217897 Then
        Kill repPartialName
        Resume
    Else
        MsgBox Err.Number & ": " & Err.Description
        Resume ExitHere
    End If
End Sub

'----------------------------------------------------------------
' Hands-On 18-4
'----------------------------------------------------------------

SELECT Employees.LastName,
  Employees.FirstName,
  Orders.OrderDate,
  Orders.ShipCity,
  Orders.ShipCountry
FROM Employees
  INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

'----------------------------------------------------------------
' Hands-On 18-5
'----------------------------------------------------------------

Sub MakeObject_Replicable(repName As String, objName As String)
    Dim repDesignMaster As New JRO.Replica
    With repDesignMaster
        .ActiveConnection = repName
        .SetObjectReplicability objName, "Tables", True
    End With
    Set repDesignMaster = Nothing
    MsgBox objName & " is now replicable."
End Sub

'----------------------------------------------------------------
' Hands-On 18-6
'----------------------------------------------------------------

Sub MakeObjectLocal_InDesignMaster(myDb As String, _
        objName As String, _
        objType As String)
    Dim repDesignMaster As New JRO.Replica

    With repDesignMaster
        .ActiveConnection = myDb
        .SetObjectReplicability objName, objType, False
    End With

    Set repDesignMaster = Nothing

    MsgBox "The object named " & objName & _
        " in the collection of " & _
        objType & vbCr & "will not be replicated. " & _
        "It has been set as local.", vbInformation, _
        "Operation Completed"
End Sub


'----------------------------------------------------------------
' Hands-On 18-7
'----------------------------------------------------------------

Sub Sync_Replicas(strRep As String, strReplica As String, _
                  Optional sncType As SyncTypeEnum, _
                  Optional sncMode As SyncModeEnum)
    Dim repDesignMaster As New JRO.Replica


    repDesignMaster.ActiveConnection = strRep

    ' exchange data and design changes between replicas
    repDesignMaster.Synchronize strReplica, sncType, sncMode
    Set repDesignMaster = Nothing
    MsgBox strRep & vbCrLf & _
        "and " & vbCrLf & _
         strReplica & vbCrLf & _
        "were synchronized."
End Sub


'----------------------------------------------------------------
' Hands-On 18-8
'----------------------------------------------------------------

Sub Get_ReplicaProperties(strPath As String)
    Dim fso As Object
    Dim mdbFile As Object
    Dim myFolder As Object
    Dim typeNum As Integer
    Dim repName As String
    Dim repMaster As New JRO.Replica

    On Error Resume Next

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set myFolder = fso.GetFolder(strPath)
        For Each mdbFile In myFolder.Files
            If InStr(mdbFile.Name, ".mdb") Then
                repName = strPath & mdbFile.Name
                repMaster.ActiveConnection = repName

            Select Case repMaster.ReplicaType
                Case jrRepTypeNotReplicable
                    Debug.Print repName & " is not replicable."
                Case jrRepTypeDesignMaster
                    Debug.Print repName & " is a Design Master."
                    Debug.Print "Design Master Id: " & _
                        repMaster.DesignMasterID
                    Debug.Print "Retention Period: " & _
                        repMaster.RetentionPeriod
                    Debug.Print "Priority: " & repMaster.Priority
                    Debug.Print "Visibility: " & repMaster.Visibility
                Case jrRepTypeFull
                    Debug.Print repName & " is a Full Replica."
                    Debug.Print "Replica Id: " & repMaster.ReplicaID
                    Debug.Print "Retention Period: " & _
                        repMaster.RetentionPeriod
                    Debug.Print "Priority: " & repMaster.Priority
                    Debug.Print "Visibility: " & repMaster.Visibility
                Case jrRepTypePartial
                    Debug.Print repName & " is a Partial Replica."
                    Debug.Print "Retention Period: " & _
                            repMaster.RetentionPeriod
                    Debug.Print "Priority: " & repMaster.Priority
                    Debug.Print "Visibility: " & repMaster.Visibility
            End Select
        End If
    Next mdbFile
    MsgBox "View Replica Properties in the Immediate Window."
End Sub


'----------------------------------------------------------------
' Custom Project 18-1
' No code. 
' Please follow the instructions in the book.
'----------------------------------------------------------------


'----------------------------------------------------------------
' Hands-On 18-9
'----------------------------------------------------------------

Sub Get_ConflictTables()
    Dim repDesignMaster As New JRO.Replica
    Dim rst As ADODB.Recordset
    Dim strPath As String

    strPath = CurrentProject.Path & "\DM_Northwind.mdb"
    repDesignMaster.ActiveConnection = strPath
    Set rst = repDesignMaster.ConflictTables
    With rst
        If .EOF Then
            Debug.Print "There are no conflicts."
        Else
            Do Until .EOF
                Debug.Print "Table: " & rst.Fields(0) & vbCr _
                ; "Conflict Table: " & .Fields(1) & vbCrLf
                .MoveNext
            Loop
            .Close
        End If
    End With
    Set repDesignMaster = Nothing
    Set rst = Nothing
End Sub


