I'm having dificulty finding out how to replicate an access 2003
database across a VPN network via a mapped drive. I have read
docs on using an "replication manager" but i can not locate such a
application. I would like to add this to a vb.net 2.0 in app
also.
You must use Indirect replication to do that. This requires the Jet
4 Synchronizer. You can get that by installing the Jet 4.0
Replication Update:
http://support.microsoft.com/kb/321076/en-us
You'll end up with a copy of the replication files in
the %CommonProgramFiles%\Microsoft Shared\Replication Manager 4.0\
folder.
To use it you'll need to set it up. Microsoft recommends using JRO
(Jet Database and Replication Objects), but I find JRO very limited
in its capabilities. I use Michael Kaplan's TSI Synchronizer,
available for download here:
http://trigeminal.com/lang/1033/utility.asp?ItemID=9#9
That page has lots of other utilities of interest to those using
replication, too, which is why I included it, even though the really
relevant page for the TSI Synchronizer is here:
http://trigeminal.com/replication/synch.asp?1033
To set up a computer with indirect replication so that you can do it
programmatically from Access, these are the steps you have to do:
1. install and register the TSI Synchronizer (creating a reference
to it registers it, but I prefer to use late binding for all
optional Access components). I recommend putting in the same folder
as your application MDB.
2. run the Jet Synchronizer (mstran40.exe) and then close it (you
have to force close it, as it has no UI). This will write the
registry keys needed for the Jet Synchronizer to be started up and
controlled by the TSI Synchronizer.
3. using the TSI Synchronizer in code (the help file shows how to
set it up), assign these properties:
a. set the dropbox location: you have to have the share created
before you do this. You can name it with a $ at the end to hide it
from anyone who doesn't know to look at it. In a Domain controller
situation, I'd give permissions for full access to the
Authenticated Users group, rather than the Everyone group, which
it will probably default to. Without a Domain controller it's more
complex and using the Everyone group is easier, though I hate to
recommend such an unsafe practice. Remember that there's two sets
of permissions, the share permissions and the permissions on the
underlying folder, and both have to be set correctly (incorrect
dropbox permissions is one of the most common reasons an Indirect
synch fails).
b. add to the ManagedReplicas collection the local replica that
you're going to synch.
c. write to the registry this key:
HKLM\Software\Microsoft\Jet\4.0\Replication Manager\SynchronizerPath
with the value a regular string being the path to the
synchronizer executable (mstran40.exe). This should be
%CommonProgramFiles%\Microsoft Shared\Replication Manager
4.0\mstran40.exe if you used the default location for installing
the replication files update you downloaded from MS.
d. assign a LogFile path/name (otherwise your log will be on your
desktop)
e. to tell the replica that it is managed, you have to do a direct
synch with another replica. These are the steps:
- using the TSI Synchronizer, start the Jet Synchronizer (with
.Running = True). It won't work if it's already running, and the
TSI Synchronizer can't control an instance of the Jet
Synchronizer that it didn't launch.
- if you're using Jet user-level security, set the workgroup
file, user and password.
- to start the synch, assign the DatabaseName property to the
name of your managed replica.
- then execute the .SynchDirect command, supplying the name of
the partner replica as the only argument.
- I have found that it's better to do this direct synch TWICE.
- shut down the Jet Synchronizer with .Running = False
- check the MSysTranspAddress table to see that the FSAddress
fields now have the Dropbox location in them and that the
TransportDescription field has the synchronizer name in it (this
defaults to the name of the machine the synchronizer is running
on). If it's not, it will probably fix itself after a couple
more synchs -- I've never been able to exactly figure out when
it actually updates this table.
Now, you should do this process on the base location, i.e., on the
server in the office first, because you want all the replicas that
go out to laptops to know about the synchronizer and its managed
replica(s) before you put replicas on the laptops.
To initialize a laptop, you'd copy the replica to the laptop and
repeat the steps above (you'll need to be logged in as an
administrator to write the registry keys needed, on all machines).
Once that's done, as long as you have an SMB connection to the
mother ship, you should be able to initiate an Indirect synch with
the remote computer (the Jet synchronizer needs to be running on
both ends; there is no way to launch it remotely). However, keep in
mind that you cannot synch indirect with a replica that is visible
in a public share. This means that on your LAN, the replica that is
used for production editing can't be your remote synchronization
hub, since to be editable on the LAN, it has to be visible to all
users connected the LAN. When you connect by VPN, you'd be able to
see that replica, too, and that prevents an indirect
synchronization.
So, you have to have a hub replica on the server that synchs on a
schedule with the production replica. This can be set up using the
TSI Synchronizer, as well (it's explained in the Help file).
So, on the server, you'll likely have:
1. Design Master, in a safe location: used only to implement design
changes to the tables, and synchronized only when pushing out those
changes, and periodically to keep it from expiring (the default
retention period is 1000 days).
2. Production Replica, in a public share on the server: used by the
LAN-connected users for all their editing.
3. Replication Hub, in am unshared location on the server (it can be
a hidden share, I think, but I've never done it that way): used by
remote users initiating Indirect synchronizations with the "mother
ship." You'll need scheduled synchs between this hub and the
production replica to get the LAN edits out to the remote users and
to get the remote edits to the LAN users.
You may choose to implement a Replica Farm:
http://trigeminal.com/usenet/usenet006.asp?1033
This gives a certain amount of fault protection for the indirect
synchs, as if the first chosen managed replica can't be synched, the
synchronizer will move on to the next managed replica and try it.
On each remote computer, you can probably have just the local
replica, and it will be managed by the Jet Synchronizer, which will
only be running when you initiate a Jet synchronization. If you want
to set up a replica farm on the laptop, you could mirror the server
setup. I tend to keep a backup replica on the laptop, that is
synched each time the application shuts down, but it's not managed.
As to the VPN, it's no different than doing the same thing across a
LAN connection, except that the VPN has to be up and running before
the indirect synch is initiated, and it will be significantly slower
than on the LAN. It's conceivable that you could initiate the VPN
connection via code, but I've never done that myself.
As to Replication Manager, it does provide a GUI for all of this,
and perhaps helps one understand how things work, but it's not a
very good tool -- once you've used it you get very frustrated with
its limitations and inconsistencies -- and it's not programmable,
anyway.
And Microsoft isn't providing it in the up-to-date development
tools -- for Office 2003 it's Visual Studio, and ReplMan is not
included. The only way to get it is to get the Office 2000 or Office
XP Developer Edition. This is bloody stupid on Microsoft's part,
seems to me, but there it is. At least they've made the files needed
available for download.
I'm crossposting this reply to microsoft.public.access.replication
since it's the only set of instructions for this that has ever been
posted there, I believe. I think you'll have more luck discussing
Jet replication there than here.
