I'm about to embark on a project whereby my client needs to be able to create replicas for use with tablet PC's that can be taken out into the field then synch'ed back at the end of the days work.
I understand that I need some careful planning so would like to put forward my thoughts for comment before proceeding and ***hopefully not making a mess*** I've done heaps of reading and here's where I'm at
background: My db is split into fe and be, the be is acc2000 and the fe is acc2007
I will be distributing this using the packaging a solution wizard
what I do know:
- db has to be split
- a copy of the fe on each tablet or workstation or laptop
- The design master should be stored somewhere safe and synch'ed periodically
- the tablets or lappies need to have a replica placed on them only once then left alone to be used and synch'ed back to the 'Mother ship' (David Fenton's term for the server replica)
What I need to know:
- how to code the replica creation for the tablets or lappies
- how to code the synching
- what to do if I need to add or change tables at the DM (I'm guessing recreating new replicas or are they automatically updated with a sync (think not))
- Is there anything extra i need to distribute with the installer, any .dll or .exe or the like (my client uses the acc2007 runtime only)
My thoughts are install the program to the users computer placing the be in whatever location, then when the user goes to use the program for the first time he has to create a replica to link to (and make him create it on their server if they have one). If the user is using a tablet PC or laptop they also have to create a replica on first use so question: Can a replica be created from the server replica or does it always have to be created from the DM?
then that should be it, use, add data etc and synch regularly
Just to make it clear- the design master should only have tables and nothing else. While replication theoretically supports synchronizing forms and reports, corruption has been reported often enough that it's good luck to avoid replicating any non-tables objects. There are already few tools for auto-updating the front ends if you need to keep them in sync on forms and reports; Bob Larson updated his tool here, and Tony Toews provided another way to do this; you can google for either.
As for the coding- This is what I usually do if I know the problem but don't know the specific syntax; use object browser and help files to get some basic information on how everything mesh together.
In this case, you need to add a reference (in VBA editor menu Tools -> References) to "Jet and Replication Library" (or something like that. Press F2 to bring up the object browser and select the library then read through the various classes, method and properties that looks like what you may need. Then in the search bar (in VBE, not Access, mind you- help files result are different between two interfaces... at least it was with 2003) type in whatever you need to know more about and see if this meets your requirement. Help files usually has a sample of code that you can copy to get started.
When you find a specific question, do feel free to post back.
I would disagree with Banana's advice on using JRO. JRO is one of the ugly stepchildren of ADO -- it wouldn't exist if MS hadn't tried to force people to use ADO with Jet data. Now, JRO has one capability that DAO lacks, and that's the ability to do an indirect or Internet synch. But since Rachel is already planning to use the TSI Synchronizer, there's no problem because the TSI Synchronizer provides a whole lot of functionality well beyond plain old DAO (including multiple methods for performing indirect and Internet synchs), as well as far exceeding the capabilities of JRO.
The only complication is that much of the MS documentation uses JRO code. I have noticed that MS is gradually providing DAO alternatives for most JRO-/ADO-only code, but they haven't completed that.
That's not as much of an issue as it may seem given that Rachel is planning to use the TSI Synchronizer, on which MS's documentation is completely silent.
I could be seriously mistaken, but when a replica is created, doesn't Access automatically add and use JRO library or was I thinking of another library?
Unless there's something special you want to do with setting replica priority, I'd simply copy the LAN replica onto the tablet PCs. As soon as that replica is opened, it will get assigned a new ReplicaID. The rest of the replica set won't know about it until it synchs with the "Mother Ship," but that should be relatively soon, I think.
If you want all of your tablet PC replicas to have the same replication priority, you could also do it the easy way by having one replica on your server that has the correct replication priority and copy that (instead of just copying the production or hub replica on the server).
Indeed, in all my years of doing Jet replication, I've never once programmed the creation of replicas. Not even once! I've always created them by copying an existing replica.
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase("YourLocalReplica")
db.Synchronize "YourRemoteReplica")
db.Close
Set db = Nothing
If you're always synching with the same remote replica, the value for the Synchronize method can be hardwired in your code. But you'll want to get the local back-end replica some other way. The easiest is to simply check the connect string for one of your linked tables:
You could also get it from CurrentProject.Connection without specifying a particular table, but that might not give you the result you expect in the event where you have more than one back end, and it's also much harder to parse (as the back end MDB path/name is embedded in the middle of the connect string, rather than always starting at the same place).
So, the code would then be:
Code:
Dim strLocalReplica As String
Dim db As DAO.Database
strLocalReplica = Mid(CurrentDB.TableDefs("tblPerson").Connect, 11)
Set db = DBEngine.OpenDatabase(strLocalReplica
db.Synchronize "YourRemoteReplica")
db.Close
Set db = Nothing
That would do the synch, but, of course, you'd want to add some error handling, and you'd likely want to check that you're connected to the network. I generally use the File System Object with late binding to do that. Here's the code:
Code:
Public Function CheckForNetwork(strResource As String, _
bolIsFolder As Boolean, Optional bolClear As Boolean = False) As Boolean
Static objFSO As Object
If bolClear Then Set objFSO = Nothing: Exit Function
If objFSO Is Nothing Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
End If
If bolIsFolder Then
CheckForNetwork = objFSO.FolderExists(strResource)
Else
CheckForNetwork = objFSO.FileExists(strResource)
End If
End Function
Calling that code should be pretty self-explanatory. In general, you'd likely use it for two different tasks:
1. for a direct synch, to be sure the server is accessible.
2. for an indirect synch, to be sure the remote dropbox is accessible.
If your remote users connect via VPN and synch and also connect directly to the LAN when in the office, it might be tough to tell the difference. But that's a complication to worry about way down the road from now!!
- what to do if I need to add or change tables at the DM (I'm guessing recreating new replicas or are they automatically updated with a sync (think not))
A change to the DM will propagate to all the replicas when the DM is synched with one of the replicas. In a scenario where you have a server with your DM, your production replica and a replication hub, the end users on the tablet PCs will get the design changes after:
1. you synch the DM with the replication hub AND
2. the tablet users synch with the replication hub.
Or, you could do it less directly:
1. synch the DM with production replica.
2. synch production replica with replication hub.
3. synch tablet replicas with replication hub.
It's important, though, to plan your schema changes really, really carefully, as it's incredibly easy to cause schema errors that are unrecoverable.
For example, if you add a table and add a field in an existing table that stores a required foreign-key value out of the new table, you're in trouble, because you've added a restraint on the values in an existing table that depends on a table that doesn't yet exist. In that situation, you'd want to do this:
1. create the new table and add the field for the foreign key value to the existing table. Do *not* make that field required and do not turn on referential integrity (these are the two steps that will cause the process to fail if you do it too early).
2. synch from the DM around the entire replica set (this is required -- all replicas have to get this change before you can go to step 3).
3. update the values in the new field you added in step 1 to have valid values from your brand-new table.
4. synch from the DM around the entire replica set so that the data gets updated in all replicas to reflect what you did in step 3.
5. only now are you ready to put referential integrity in place -- the data has to conform to the RI *before* you can synch the RI changes. So, you add RI in your DM and synch around the replica set.
The reason you have to break it down into these steps is because:
SCHEMA CHANGES PROPAGATE BEFORE DATA EDITS
If you try to update your data to reflect the new RI in the same synch that you implement the RI, you'll end up with a deadlock, because the RI will get synched first, and at that point the updates to make the data conform to the new RI rules will not have been propagated because:
SCHEMA CHANGES PROPAGATE BEFORE DATA EDITS
So, you always have to lay the groundwork very carefully, and make sure the data is updated first, then you synch with all the replicas, and only then can you implement your new referential integrity restraints. The same applies to validation rules and the REQUIRED property of fields.
But in answer to your question, yes, all you have to do is edit the DM.
For a commercial project where you have different clients using your app, it's best for your to have a non-replicated version of your back end that is used to create new replica sets for each replication site. Otherwise, all your customers' replicas will be members of the same replica set, which would be *very bad*.
- Is there anything extra i need to distribute with the installer, any .dll or .exe or the like (my client uses the acc2007 runtime only)
My thoughts are install the program to the users computer placing the be in whatever location, then when the user goes to use the program for the first time he has to create a replica to link to
I'd have your installer place the copy of the replica on the laptop. You'd have to be connected to the server to create a replica from it, anyway, so I don't see much difference. You could include a replica as part of your installer package, but you'd have to keep updating the installer package each time you made schema changes. On the other hand, there really oughtn't be many schema changes once the app is in production use -- if there are, then you went into production *way* too early!
I would suggest that it might be easier to just have your app check to see if it's running for the first time, and if it is, ask where it should copy its replica from. The user will have to know the answer to that question, though.
If the user is using a tablet PC or laptop they also have to create a replica on first use so question: Can a replica be created from the server replica or does it always have to be created from the DM?
Any replica can be used to create a new replica, but the replica priority of a newly created replica is always only 90% of the parent replica. So, your DM is always 100, and if you create a replica from it, that one is 90. Replicas created from that one are 81. Replica priority is important to think about because in the event of a synchronization conflict (i.e., the same data has been edited in two replicas), the replica with the higher priority wins. You basically want all your editing replicas to have the same priority, which is one reason why I think it's easier to copy a replica than to use the MakeReplica command to create a new replica in code.
I could be seriously mistaken, but when a replica is created, doesn't Access automatically add and use JRO library or was I thinking of another library?
I don't believe I've ever seen that happen, since it's completely unnecessary for using replication. Replication needs nothing but DAO in order for it to work, as it's a core functionality of Jet itself. I have not one single Jet 4 replica or front end using a Jet 4 replicated back end that has the JRO reference. I don't use JRO, ever, because it is simply worthless.
Hi everyone, thanks for all your replies. I'm beginning to have some confidence with what I'm doing so thanks, but I still have some questions regarding how to structure the whole system. I have the tsi synchroniser working no problem, and distributing the synch40.dll will be ok (although I will need to learn how to register it as part of a clients install, they will all be remote from myself so it needs to happen without intervention)
Is it correct to have each user with there own replica or could workstations all use the same one?
How is the synch'ing back to the design master conducted, does one main person have to know to do this or can it be done programmatically on a schedule?
I get a bit confused with the tech talk that goes on ie JRO DAO IIS etc but am happy with the tsi and the option to indirect synch down the track (no internet!!) Just need to get my head around the stucture as we are planning to sell this to all the big vineyards in the world (...doesn't hurt to aim high!!)
PS thank you David for your lenghty response to my questions...I've just been examining it in full and your help is very much appreciated...your a gem!!
Hi all, thanks for your time, I think I'm getting somewhere but just have one small quick question;
Here's the code David helped with that I've implemented to do the synch
Dim strLocalReplica As String
Dim db As DAO.Database
strLocalReplica = Mid(CurrentDb.TableDefs("Company1").Connect, 11)
Set db = DBEngine.OpenDatabase(strLocalReplica)
MsgBox "Synchronizing commenced, please wait!"
DoCmd.Hourglass True
db.Synchronize (Me!txtSyncTo)
db.Close
Set db = Nothing
DoCmd.Hourglass False
MsgBox "Complete", vbInformation, "Davidson Viticulture"
This works like a treat and seems to simple to be true but am wondering how it will work on the Tablet PC (unfortunately I don't have $4000 to go buy one with for testing and my client is on the other side of the country) so want to be ready to handle any potential problems.
The tablets don't have full versions of access on them, only the runtime, do I need to distribute anything extra to the tablets? I've read alot about making sure the synchronizer is running...and needing Jet Replication installed...sorry if these questions are dumb, still on the steep learning climb.
Hi everyone, thanks for all your replies. I'm beginning to have some confidence with what I'm doing so thanks, but I still have some questions regarding how to structure the whole system. I have the tsi synchroniser working no problem, and distributing the synch40.dll will be ok (although I will need to learn how to register it as part of a clients install, they will all be remote from myself so it needs to happen without intervention)
Once again, I really question why you are using the TSI Synchronizer. If the synch is from PCs connected directly to the LAN, you don't need to go to all the trouble of setting up indirect replication, and if you're not using indirect replication, then there is simply very little utility in adding a dependency on the TSI Synchronizer when you can do all the things you're contemplating without needing it at all.
Is it correct to have each user with there own replica or could workstations all use the same one?
Eh? Each disconnected tablet PC has to have its own replica for editing in the field.
On the server, you'll have a single hub replica that is used for synching, and, ideally, that won't be the same as the production replica used by workstations connected full-time to the LAN. You'd then schedule synchs between the replication hub and the production replica. Don't even think about synching those two as often as allowed (every 15 minutes). If your users need data that up-to-date, then you're entirely using the wrong technology. Secondly, synchs are inherently dangerous when they happen to a replica that is being edited, because it can corrupt data in certain circumscribed cases. All memo fields in an app with a replicated back end where scheduled synchs occur while users are editing data should be done with unbound fields. That is, you have an unbound textbox on your form, and in the form's OnCurrent event, you set the unbound textbox's contents to the value of the memo field (from the form's underlying recordsource):
Code:
Me!txtMemoField = Me!MemoField
In the control's AfterUpdate event, set the underlying recordsource's field value to that of the unbound control:
Code:
Me!MemoField = Me!txtMemoField
That's it. That's all there is to editing a memo field unbound.
If you want even more protection, you should contemplate moving your memo fields out of your main table and into a table with memos only. If your main table has more than one memo field, you have two choices:
1. a single 1:1 record in a memo table with multiple memo fields, OR
2. multiple 1:N records in your memo table, one for each memo type for each record. In that case your memo table would have the foreign key, a field to indicate the memo type, and then your memo field.
How is the synch'ing back to the design master conducted, does one main person have to know to do this or can it be done programmatically on a schedule?
No. Don't synch with the DM on a schedule. When you make a design change to your DM, your last step to propagate your design change is to synch with one of the replicas. Your replication hub would be the obvious choice, but it could be the production replica, too. A synch schedule is simply a bad idea -- don't synch with your DM when you don't have anything to synch (except to keep the DM from expiring, which means at least once every 1000 days, unless you've changed the default retention period)!
I get a bit confused with the tech talk that goes on ie JRO DAO IIS etc
Ignore it. You don't want to use JRO. You don't want to use Internet replication (which is where IIS comes in) under any circumstance. DAO + TSI Synchronizer is all you'll ever need. And you don't even need TSI Synchronizer until you want to do something beyond unscheduled direct replication.
but am happy with the tsi and the option to indirect synch down the track (no internet!!) Just need to get my head around the stucture as we are planning to sell this to all the big vineyards in the world (...doesn't hurt to aim high!!)
Again, I just don't understand why you insist on using the TSI Synchronizer. You are making things way, way too difficult for yourself when the base scenario you describe has no requirements that cause you to need anything beyond plain old DAO.
Thanks David for your reply, I have realised in the last week that I don't need TSI so have scrapped it and have the suggestions you made working fine, thank you ery much for your time.
Thanks David for your reply, I have realised in the last week that I don't need TSI so have scrapped it and have the suggestions you made working fine, thank you ery much for your time.
Your experience with the TSI Synchronizer won't be wasted if you decide you want to support indirect synchronization for remote users over a VPN, or if your users want to use wireless networking to connect to the LAN (not reliable enough for direct synchs).
Thanks David again for sharing your indepth knowledge of Access replication! Many little gems there! Even after a year your advice is still helping others, including me!
