Two or more users attempting to sync with the hub at the same time

Two or more users attempting to sync with the hub at the same time

Post by rdemya » Sun, 17 Dec 2006 01:10:00

I'm just starting to play with replication. It looks like it might
fairly well for what I need.

I'm not in a good position to test what happens if two or more users
try to
sync their replicas at the same time with the hub replica located on

Can someone who has experience with this provide me with some guidance.
reason I ask is that I was thinking about having my app sync the local
replica with the server hub when the user closes the app (using code).
But I
wasn't going to force the user to wait for a messagebox stating whether
was sucessful or not, in case, it takes a long time. But if
synchronization results in failure, then I'm not sure what to do. If
error message doesn't display, then the user will merrily close the app
be angry later when they find out that the syncing failed because of a
simultaneous synchronization failure.

So how does Access handle this situation?

If this is a problem is there some way that I can set a flag in the hub
which will alert the main app that syncing is currently occuring
between the hub replica and another replica. While I now how to code
that in general, it would probably have to be handled somewhat
differently with replicas, because the value of the flag would be
replicated as well which could cause problems when other replicas try
to sync up.


Two or more users attempting to sync with the hub at the same time

Post by David W. F » Sun, 17 Dec 2006 02:32:19


With DIRECT replication, this would be a problem. With indirect, it
oughtn't be an issue, I believe.

I've never encountered a problem with it.

I assume you're meaning an indirect synch, then, as for a direct
synch launched on application close, the app won't close until the
direct synch is finished (a number of my apps synch with a backup
replica during application shutdown).

Why don't you try it?

It's not Access that's involved, but Jet. I've never encountered it

I honestly don't think it's a very good idea to do an indirect synch
every time the app shuts down. If your users are always connected to
the Internet, I wouldn't be using replication at all, but use
Terminal Server, instead.

If they aren't, then launching a synch on close isn't a very good
idea, in any case.

Try it and see what happens. Please report back here, as I've never
seen anything about this subject before.

I expect that it would not cause a problem. My guess is that the
synchronizer will serialize the synchs in the order that they were
requested. But I could be wrong on that.

David W. Fenton
usenet at dfenton dot com


Two or more users attempting to sync with the hub at the same time

Post by rdemya » Sun, 17 Dec 2006 03:23:08


Thanks for the response. Based on your responses, it appears that I'm
on the right track with my plan.

I would prefer to use indirect synching, but the IT environment here
doesn't allow any executables to be installed, users have no admin
rights, many e-mail attachments are automatically removed; so you get
the idea. I believe that indirect requires Replication Manager, and
the TSI app would be highly recommended (neither of which can be
installed). And don't even think about making changes to the registry.

Hence, I think I am left with direct synchronization.

My main app is setup so that most users cannot add/edit/delete data.
The app primarily uses data from other systems and cross references it
to help users better do their jobs. Only administrators have rights to
add/edit/delete. Typical ratio is 2 users to 1 administrator.

My goal is to make this synching process transparent to the user. I
think I can do this when the synch is initiated from the 'local' copy
to the server hub replica copy. My thought was to use a "star"
topology. Also, I'm not sure if a replica farm on the server is
required in this instance.

I'm asking a lot of questions before I get started, because I get the
feeling that this can be a complex topic for the novice and I'm sure it
will require a fair amount of time to implement.

David W. Fenton wrote:


Two or more users attempting to sync with the hub at the same time

Post by rdemya » Sun, 17 Dec 2006 05:23:57

reliminary testing on simultaneous synchronization:

1) I created a hub replica of my back-end file (tables only) on my
laptop. Then I created a replica in another location on the laptop. I
also created a replica in a folder on my desktop machine.

2) I have a small test app that I coded to create replicas and
synchronize replicas. I have the test app on both my laptop and
desktop. For each machine, the back-end files to be synched are the
replicas on each machine with the hub replica located on the laptop.

When I clicked on the synchronize button in the app on both the laptop
and desktop, the laptop synchronization was faster than the desktop.
With no actual updating of records, an error (Could not lock file) was
only generated two out of ten times. In every case the error was
generated on the desktop. This would be the machine where
synchronization would be slower because the hub replica is located on
the laptop.

When I deleted 200 rows from the hub replica and then tried to
simultaneously sync the laptop replica and desktop replica with the hub
replica, in all but one test no errors occurred. In the test with an
error it was the same "Could not lock file' error and on the desktop.
In the successful tests, it appeared that synchronization on the laptop
was slower than in the earlier test where synchronization occurred
between replicas where nothing had been updated.

I then deleted 2000 rows from a table in the hub. Same test: clicked
on both sync buttons at the same time. The laptop machine synchronized
within one to two seconds. The desktop machine took noticeably longer
(8 to 10 seconds or so) but it too synchronized. Only one test.

So I think Jet tried to manage the simultaneous synchronization. It
was very noticeable in the 2000 delete test that synchronization on the
desktop was taking longer than before, but it occurred without

But I'm not sure these tests will translate well to my user's slow
network environment. These tests are all I have so far. I'll have to
test it in the slow network environment and will report back.

But if I do get errors like this during shutdown of my main app (where
I am trying to synchronize), it looks like I could trap for the error
and write an error message to a front-end table. Then the next time
the app opens, the user could get a notice saying that the
synchronization failed the previous time the user closed the app.

Seems like it should work, but since I'm a newbie on this, advce,
suggestions are always appreciated.



Two or more users attempting to sync with the hub at the same time

Post by David W. F » Sun, 17 Dec 2006 07:09:45


No, it only requires the synchornizer. An explanation of how to set
up Indirect Replication without ReplMan can be found in this post of
mine (all on one line):

That's unfortunate that there are no circumstances in which they
allow that. They certainly allowed the installation of MS Access,
which meant major modifications to the registry and the installation
of a great deal of software. It's *** y stupid on their part to
prohibit such actions in all cases, instead of evaluating on a
case-by-case basis if it's OK or not.

What's the connection between the remote replicas and the replica
they'll be synching with? If it's a LAN connection, direct is fine.
If it's not, you're completely out of luck if you can't use
Indirect, as it's just not possible to safely synch across anything
but a wired connection of at least 10mbps in bandwidth.

Why is replication needed, then? Replication makes sense only when
the data editing has to take place in two locations. If it's
happening in only one, you don't need replication at all -- all you
need is an updated copy of the data, copied over top of your old

Replica farms are only useful when you're using Indirect

If you can use Direct, then you're on a LAN. If you're on a LAN,
then I have no idea why you need replication at all. If you're not
on a LAN, you can't use Direct. And if you can't install the
software for Indirect replication, then you can't use that.

And it's not clear to me from the description of your app that
there's any real need for replication in the first place.

David W. Fenton
usenet at dfenton dot com

Two or more users attempting to sync with the hub at the same time

Post by David W. F » Sun, 17 Dec 2006 07:13:59


I would *never* implement simultaneous synchronization with DIRECT
replication, only with Indirect. Since you say you can't use
Indirect, then I think you have a major problem.

I would expect that a direct synch collisions would generate an
error, because it has to lock the remote file in order to perform
the synch.

When nothing has been updated, it's not really a test for
replication, as all that has to be done is a read of the replication
system tables. It's only when there have been changes that you'd be
actually testing the simultaneous synch scenario.

Testing just DELETeS is not a very good test. Updates are going to
be the problem in a synch collision.

How slow? If it's a LAN (10mps or 100mbps), it should be fine. If
it's a WAN or an Internet connection, then you just can't use

That sounds reasonable to me, as long as that's not a problem.

Again, DIRECT is only usable in a LAN environment (and wired --
wireless is not suitable for any form of work with Access). Anything
else and you need INDIRECT replication.

David W. Fenton
usenet at dfenton dot com

Two or more users attempting to sync with the hub at the same time

Post by rdemya » Sun, 17 Dec 2006 08:27:45


As usual you're asking all the right questions.

So, okay, here's the deal. My app is mostly about complex calculatons
and providing views on cross-correlated/cross-referenced data coming
from a number of systems that do not talk to each other.

The connections to the server are excrutiatingly slow. Distances
between users and the server range from 0 miles to 100 miles. I
believe it is a fairly robust network since the server hardly ever
drops out and users routinely copy important files from their local PCs
to folders on the server. I don't know if the connection 100 miles
away goes over a WAN. However, it is incredibably slow to use my
application when the back-end is on the server. Also, as I noted, my
app does a lot of complex calculations and analysis. If the back-end
file is on the local PC, a typical analysis might take two minutes
(multiple tables, tens to hundreds of thousands of rows with calcs on
each row, then comparisons of the results and finally display the
filtered results that meet the comparison criteria). If the back-end
file is on the server, for the people 100 miles away, well they should
just go to lunch.

Realizing that no one would use my app unless I could increase the
speed of data retrieval, I decided to use a launching program to open
my app. On the launcher, the user can quickly select to connect to
back-end file on their local PC or the server. There is also a
download checkbox, that will download the back-end file from the server
to the local PC. This all works very well and we have been using it
for 6 months this way. The users appreciate the speed of connecting to
the local PC back-end file. When they have to download the back-end
file, however, it can take 10 to 15 minutes just for the download
(around 125 MB).

By now your saying, this is crazy for a system where users need to
update data. Our saving grace has been that updating of data typically
occurs only once or twice a month. Hence our plan was to just inform
users of the update to the server backend by e-mail (sent automatically
from my app). The next time they logon they check the download box and
download a fresh back-end file. BTW: all users are well aware that
the data in their local PC backend may not be up-to-date. In 6 months,
this has never been an issue.

Let me just say, if the users have to connect only to the server
backend, they'll never use the app. While the app is very useful it is
not mission critical.

Updating of data in this scenario is an issue. To connect to the
server backend and update data can be tedious because it is so slow.
Although we update infrequently, when we do, it can be a lot of data.
So far, I've been handling all of the updates but this is not an ideal
scenario for me.

Now I've discovered replication and this could be a major answer to my
problems. Since replication seems to only update incrementally, this
can be much faster than downloading back-end files. Rather than
download (file copy) updated back-end files, users can instead synch
their local replica with the server hub replica. Further I think this
could be done without user intervention (i.e. I need to see how long it
takes; but I'm sure it is faster than file copy). Also, administrators
can now update tables in their local back-end and then synch it with
the server hub replica. This is why I was wondering about synching the
backends upon closing the app.

Two or more users attempting to sync with the hub at the same time

Post by David W. F » Mon, 18 Dec 2006 03:47:13


That's the test -- if the connection is too slow to us the back end
across that connection, then it's too slow for direct replication.

A direct synch has to download the back end, too.

I think you mean when they *don't* need to update data, no?

Sounds like you don't need replication at all, then, just a

I never suggested such a thing.

Direct replication can't be much faster than that. Indirect *would*
be much faster, though.

Yes, but a direct replication still has to open the remote back end
across the wire. Indirect is very efficient, though, as it only
works on the local replica and then exchanges messages and data with
the synchronizer on the remote computer.

Have you tried downloading a zipped copy and unzipping it locally?
That would likely reduce the download time by a factor of 4 or more.

If you've got a full-time connection, I'd be hosting the whole thing
on Terminal Server in my main office. That would mean everyone would
be working on the same data, all of it local to the machine where
they're running the app (i.e., the Terminal Server).

Dunno why you're going on about that. I didn't suggest such a thing.

Terminal server is the best solution.

Indirect replication would be next.

Easiest would be the zipped-up file copy. You can get VBA code to
unzip files in Access. Another option would be to shell out to a
batch file, which runs asynchronously (i.e., the unzip command
wouldn't run until after the file copy had completed).

David W. Fenton
usenet at dfenton dot com

Two or more users attempting to sync with the hub at the same time

Post by rdemya » Mon, 18 Dec 2006 04:12:30

Thanks for the reply.

LOL. In the past when I've posted on my method, I usually get flame
responses, so I guess I'm a bit defensive.

As I mentioned earlier, IT has total and absolute control over users
machines. While they stop everything else, they allow Access because
so many departments have Access databases. I know they consider Access
to be a toy; if they only knew how powerful it can be.....but enough
about that. I don't think terminal server is a possibility since
wouldn't it require a client version on each machine. Plus the data is
too sensitive to be hosted off site. (BTW: IT used to allow use of
Windows Scheduler; now they've taken that away as well).

True, and in fact it takes longer because my backend file (after
replication) is now twice as large as before. But it could potentially
be a one time event or maybe once every six months to a year. With the
current unreplicated method, it is at least a once a month event.

What I really want replication for is so that the data entry users can
update their local PC backend and then sync it with the main hub
replica on the server. The downloading method that I'm currently using
does work; but it's the "uploading" of data that is currently still a
problem, because data entry personnel will need to connect to the slow
server backend.

I would kill to use indirect synchronization. Can I use it in the
environment I've described. In another post you've noted that one
doesn't really need Replica Manager. Further, the TSI utility isn't
really needed (although highly desirable), and JRO can be used if
that's all that is available. But I guess the Jet Synchronizer is
required and would have to be installed. This is probably going to be
a problem.

You mentioned the test with regards to slow connections. I've added
the make replica and synchronization code to the app and will give it a
try on site next week with test backends. I'll report back on how slow
the synchronization method is.

Again, any other ideas you have on using indirect sync in my situation
would be greatly appreciated.

David W. Fenton wrote:


Two or more users attempting to sync with the hub at the same time

Post by rdemya » Mon, 18 Dec 2006 05:58:50


In an earlier post you responded to my following comment:

with this response:

I know you have the experience, but I'm not really convinced. Here's
why. When I update the application, I put a copy on the server in a
folder titled "Updates". My launching application always checks to see
if there is a file in that folder with the same name as the
application. If so, it opens that file on the server to check the
version number which is located in a local table. If the version number
is "higher", it informs the user that there is an update to my app and
would they like to download it. Even on these slow servers, this
proces of opening the updated app on the server to check the version
number in a local table is reasonably fast.

Hence I guess this is why I've been anticipating that the direct
synchronization might be reasonably fast (reasonable in our situation).
Even if it takes 2 or so minutes this would be wholly acceptable.

I'll be trying it this week and will report back.



Two or more users attempting to sync with the hub at the same time

Post by David W. F » Tue, 19 Dec 2006 14:38:08


Windows XP comes with the Remote Desktop client. For Win2K it's an
easy download/install.

Huh? Why would it be off-site?

I simply don't see a role for replication here.

Ah. I thought they weren't editing.

The Jet synchronizer is, in fact, required.

David W. Fenton
usenet at dfenton dot com

Two or more users attempting to sync with the hub at the same time

Post by David W. F » Tue, 19 Dec 2006 14:41:27


Checking one property does not require loading anything but the
headers of the remote MDB. Synchronizing requires pulling the full
indexes for the s_Generation field for every single replicated
table. It doesn't necessarily pull the full table, but it's going to
pull a lot of data pages, especially if there's been a long time
since the last synch.

Speed is not the issue -- the danger of corruption is.

Don't do it -- it's way too dangerous.

David W. Fenton
usenet at dfenton dot com

Two or more users attempting to sync with the hub at the same time

Post by rdemya » Tue, 19 Dec 2006 16:37:37

> Don't do it -- it's way too dangerous.

I don't see how it is more dangerous than connecting to the back-end
files on the server and editing the data that way. In both cases isn't
data being passed over a slow and possibly unreliable network. Or is
there something inherently more dangerous about the syncing process?


Two or more users attempting to sync with the hub at the same time

Post by David W. F » Wed, 20 Dec 2006 05:12:03


Yes! Corruption in non-replicable databases is often easily
corrected with a compact, but in a replicated database, the
slightest corruption (usually not even real corruption, just a flag
that doesn't get set) will cause the replica to lose replicability.
That means it's no longer able to synch with the other replicas and
never will be able to synch ever again. Any data that hasn't been
synched will have to be recovered somehow (it can be done with a
bunch of queries if you understand the replication fields), and it
takes a very long time.

I've been there, and done that, and it's just a terrible thing to
risk -- much worse than editing a non-replicated back end across a
slow/unreliable connection.

David W. Fenton
usenet at dfenton dot com