Programming partial replication (1 Viewer)

[Quirkey]

During the process of creating a partial replica using the Partial Replica Wizard, a dialog box is displayed which lists tables that my filter does not directly apply to. As stated in the dialog box, "tables that aren't bold aren't related to the filtered table with referential integrity enforced. To include all records, leave a table selected".

The Partial Replica Wizard does the job well. However, I am unable to do this using VBA. I put the following code in my CreateFilter sub:

For Each td In db.TableDefs
If Not InStr(td.Name, "MSys") > 0 Then
td.ReplicaFilter = True
End If
Next

‘Set the filter on tblUserprefs
Set td = db.TableDefs(“tblUserprefs”)
td.ReplicaFilter = “Site = ‘Box Hill’”

It works insofar as only those users located at 'Box Hill' show up in the replica's Userprefs table (good), and all the unrelated tables ARE present in the partial replica. However, when it comes to setting relationships in the partial replica, the code I am using has no impact on what appears in tblUserdata of the replica:

For Each Rel In db.Relations
If Rel.Table = "tblUserprefs" And Rel.ForeignTable = "tblUserdata" Then
Rel.PartialReplica = True
Else
Rel.PartialReplica = False
End If
Next Rel

I hoped the code would filter the contents of tblUserdata in the replica such that only data related to the users in the Site 'Box Hill' (as established with the filter on tblUserprefs) would appear in the replica.

Any help would be greatly appreciated. I'm losing my sense of humour (and a few kilograms, so it's not all bad)

Quirkey

 

[dfenton]

I can't really help you on this one, as I never saw the utility of partial replication, so have never used it. My only suggestion would be to check if JRO has different support for this than DAO. It is certainly the case that MS purposefully screwed things up by adding support in JRO that was lacking in DAO (such as the ability to initiate an indirect synch in code without the TSI Synchronizer), so maybe they enhanced the ability to do partial replication there, as well.

Also, keep in mind that Jet Replication is on the way out, and that it doesn't function well in Vista/Win7/Server 2008 environments, nor in 64-bit environments (without lots of extra work). Sharepoint is MS's new recommendation for doing what Jet Replication did, and frankly, I can't say that it's not a good thing (you don't need the expensive version of Sharepoint for this, so far as I know).

 

[Quirkey]

Mr. Fenton, I am honoured sir. Thank you for your reply. I managed to get it working by eliminating the loop through all the 'non-system' tables, instead making specific reference to those unrelated tables which I wanted fully replicated in the partials.

I spent a lot of time thinking about the advice of Li Kwai Kwat to his disciple who asked, ' Master, what should I do if I am hanging off the edge of a cliff and being approached by a
ravenous tiger'. To which the venerable sage replied, 'Don't get yourself into that situation in the first place!'

That just about sums up my feelings on Access replication.