Synchronize Access Databases by the Record, Dfs?

Hello All, I am looking for advice on sharing an Access Database across a WAN.

We have win2k3 servers at two locations on either side of a VPN. These servers are domain controllers for a common domain broken up into two sites. Both sites need to be able to create reports from and make changes to the data in a common Access DB. With the DB stored on one server, working from the other location (across the VPN) is painfully slow. We have implemented a Distributed File System to share other data and is working very well as expected. It would work very well for our database aswell except that whenever any record is changed, the entire file must be transfered to synchronize the copy at either end. This creates an oppertunity for error in that two people at either location could be working with their respective database, and somebody's work will be overwritten.

Now on to my question; is there a utility within Win2k3, or a piece of 3rd party software that will synchronize duplicate access databases by the record in in real time?

One option is to create a replicated database. This will allow you to make changes to the data and Access will handle all the complexities of synchronizing data from one copy to the other.

The downside to this approach is that only one copy may make DESIGN changes to the application. The upside is that deploying these design changes are snap. Basically the developer makes the design changes then synchronizes with the server. As soon as the satellite locations synchronize with the server they inherit any design changes that get passed along at the same time they send and receive any data that has been changed or updated on either side.

If you use a replication server the process gets even better as the servers between locations can be taught to periodically compare notes and update each other with the most current design changes and data on both sides. Additionally there is a built in method for resolving conflicts (ie: two users from different lactations update the same data, same field, same record where the changes made do not match. In cases like these you have a simple interface that allows you to accept either users change as the overriding choice or offer an entirely new one of your own that overrides everyone.

This should solve your network lag issues in that everyone at each location is in effect working with a local copy that is as current as the last date/time when it was last synchronized. Furthermore these replicas serve another role. They are sort of like off sight backups. If one should become corrupted for any reason spawning a new replica is trivial and the only data lost is that data entered into one of the replicas between the time it was last synchronized and when it got corrupted.

One more benefit to this approach is that it doesn't require you to write a single line of code to implement as the feature is built into access.

********************************

If for some reason you find the above method objectionable there are other alternatives. One of course you could use remote desktop connections to connect your machine to another at a remote location. In cases where there is plenty of bandwidth this can work fine but in cases where there is not it rapidly becomes undesirable.

********************************

The replication option above, however, can be made to work even when the satellite locations lack persistent connections or for that matter a fast connection. To put this in perspective, I've deployed replicated databases in scenarios where the satellites had nothing more than a 28.8 baud modem to periodically connect themselves to the internet. Updates can take a few minutes but that’s only when the database is getting it’s updates, the rest of the time, (that being when the user is actively using the app), performance is high because they have in fact a local copy from which they do their work.

Hello Rick Thank you very much for the reply, I have a question for you though; where should these replicated databases reside? Should they reside in a standard folder on each server or in a folder that is being replicated by FRS (as in the distributed file system we tested with)?

As I understand your message, Access itself will handle keeping both copies in the replica set up to date? If for example we make a change to a record in the copy at our site, Access will immediatly send instructions to the replica at the other site to update that record?

How does the DESIGN change get replicated? Does Access handle that aswell?

Using Terminal Services (remote desktop) here is a good suggestion, but currently we can't justify the extra licensing costs to implement it. Another type of solution would be preferred, like the above (provided I understand you correctly).

A replicated database should reside in a location where it is most accessible to the users who must use it. Note: That Access Replication has nothing to do with File Replication Systems so the two should not be confused and if you are replicating files and/or folders via FRS (File Replication Systems) the Access Replicated Database SHOULD NOT reside there.

Access does handle all the detailes regarding keeping it's replicas up to date. In other words any changes to records are meticulously keep track of by Access and passed on to any other replicas within the replica set.

Design changes are created and distributed by a single Replica known as the Design Master. Only this replica is allowed to make design changes to the database and those design changes are disseminated to other replicas in the same manner as the other replicas disseminate and receive any record changes.

Synchronization DOES NOT happen automatically unless your replica is being managed by a server (often time's known as a hub master). Servers can be set up to synchronize with each other periodically and automatically but absent that the user must initiate the synchronization process. Initiating synchronization isn't difficult, as one simply selects the pull down menu named Tools, Replication and then Clicks Synchronize now. They will be offered some choices regarding what database they would like to synchronize with but after making that selection (which will probably be made for them by default) they need only click OK. As a general rule it is best to synchronize when you begin (so you start off with all the latest updates) and then again when you are finished, (so that your changes are then shared with everyone else).

Synchronization can avoid the need to rely on Terminal Services and other licenses since the server can be set up to sychronize of an IP address which also means it can be done over the interenet. Bear in mind that Sychrnonization that involves a Server Hub relies on the instalation of Replication Manager, (A Small and Simple to use program), that comes on the Office 2K Developer CD.

Rick

0

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear? Sometimes you may have an additional problem: where the devil did they go? If you last had a popup form open…

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…