Re: Homegrown synchronization

"rdemyan" <rdemyan@xxxxxxxxxxx> wrote in
news:1167277576.600997.149500@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

David W. Fenton wrote:
"rdemyan" <rdemyan@xxxxxxxxxxx> wrote in
news:1167266561.984368.281860@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

Here's a suggestion:

Have a separate front end with linked tables to your shared
database. Have the synch program use that back end, instead of
the real shared back end. Then, before it runs, have it check to
see who is using that front end, and if anybody else is using it,
prohibit it from running. You might also do things like try to
delete the LDB file, which would mean that it's actually *not* in
use. You can use the ADO UserRoster for this, or the LDBUSR.DLL
(look that up on Microsoft's website, or on the Access Web,
mvps.org/access).

You totally lost me on this one. By a separate front end, you
don't mean my main application, do you? Are you suggesting that
the sync program ("front end"?) I wrote should link to the shared
backend file on the server?

Know. I'm suggesting that instead of using the back end directly,
your synch program could use an intermediary MDB file with linked
tables and the only purpose of this intermediary MDB file would be
to provide you with an LDB file that would allow you to restrict to
one user (something you can't do when using the real back end).

Ah, yes, just thought of a different method: store the synch app
on the server, and use *its* LDB file to figure out if it's
already in use. That makes more sense. And you could make sure it
launches exclusively.

Store the sync app on the server.... hmmm...... So the idea is
that if it is on the server, I could set the option to have it
open exclusively.

No. That's not an MDB option, but a program option. And I wouldn't
open it exclusively, because then you lose the possibility of having
two users running it as backup. Instead, I'd allow it to be opened
by more than one user, but only one user at a time allowed to
actually run the synchs. This would mean you'd have to have your
synch timer run code to check how many users have it open. If User1
opens it, he'll be running the synchs. If User2 then opens it, her
copy will then wait until User1 logs out before it will ever run any
synchs.

Is your concern that if two users have the synch app open
on their machines and both synch apps try to process an update
that they find, that that will cause a problem. Frankly, I hadn't
considered that!

Yes, of course it will cause problems. From my point of view you
want:

1. only one user at a time running the synchs.

2. guarantee that the synchs will run.

To get #2, you probably need more than one user running the synch
program at a time, so that if somebody shuts down her PC, there will
still be a copy of the synch program running. So, to get #2, you
have to have #1 written in a way that it checks constantly if it's
the only user and only starts running the synchs when it is.

I foresee an issue with this -- you'll never know for certain
when you can delete the files in the export dropbox.

Yes, clean up of these temp updateDBs will be an issue. How does
Jet indirect synchronization handle this? It's sending message
files, isn't it? Couldn't I just copy the technique used there?

It's sending a different set of message files for every replica
pair. My understanding was that you'd be creating a single file for
each "generation" and multiple remote databases would process it. If
you do it like Jet, then you could safely have the remote database
delete the message file as soon as it successfully closes the
transaction in which the synch operations were performed.

Seems to me the import update process should create the export
database. That way, each import creates a new export. This would
be like a Jet replication generation number.

Yes this is my current plan. When the sync app processes an
import to the shared backend, it then creates a temp updateDB
based on the shared backend for EXPORT to the remote users.

If you do it this way, rather than creating a message database for
every remote database, you'll have to keep track of which remote
databases have processed the message database. When all of them have
done so, you can archive it.

Ah, I see the issue. The problem is updates to the back end by
LAN users, which aren't done by imports. I'd use my synch program
to run an export on a schedule, then (basically the same way
you'd do with a replication hub and a production replica).

Actually updates by LAN users is an issue that occurred to me
after I posted previously, and I've been wondering how to handle
that. Scheduling has its advantages, but it could also mean a lot
of updating of remote user apps that are unnecessary. On the
other hand, if there is virtually no data in the temp updateDB
sent to the remote users, then that import will only take a few
seconds, which would not be noticeable since my app takes about 15
to 20 seconds to load anyway. But the file copy from the server to
the remote machine will probably take longer. Originally I had
thought that the model for the temp updateDB would be relatively
small. It's actually 2.5 MB when it is completely empty of data.
So, it's at least 2.5 times larger than I thought. Copying it to
remote machines over a WAN might take a little time (but is
probably not worth worrying about).

That's where zipping comes in. If you zip it up, it's like to be 1/4
the size or less. That can very easily be automated.

Yes. There's be no separate process required for deletions.

But that's pretty complicated.

I'd just go with the delete flag and not worry about ever purging
the actual records.

The downside, of course, is the code changes that will be
required to my app.

Here's where a tool like Speed Ferret can come in handy. It
allows you to do global search and replaces, so you could write a
query that filters out the deletes in a table, and then search
and replace for every place where the table is used and replace
it with the query.

Another alternative is to rename the base linked table and
replace it with a query named the same as the original table link
that filters out the deletes. This would mean you don't have to
edit everything.

Yes, I thought of this too because I already have RWOP queries. At
first, I thought I could just modify the RWOP queries, but this
won't work because the users can't get at the tables directly with
the security. The only way they can get at the DeleteFlag is
through the RWOP queries. So, I'd have to rename the existing RWOP
queries with a prefix and then create new queries with the old
names of the RWOP queries and adding "WHERE DeleteFlag = False).
This will add another layer of queries (pushing it up to about 300
total in the main app).

If I implement the delete flag system, I will do this though, as
there is no way I can go through all of my code to find all of the
select statements, etc and modify them. Speed Ferret could help
with this as well??

Absolutely. It's a search and replace program. I'm afraid you'd
still have to run the search/replace for each table, though.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: Replication ...on wireless LAN?
    ... A wireless LAN may have sufficient bandwidth to make a direct ... it is not advisable to do a direct synch over ... synch opens the remote database across the wire. ... The time and cost of implementing indirect replication is ...
    (microsoft.public.access.replication)
  • Re: Internet replication
    ... > copies of the database on laptops. ... I recently moved my website to ... Internet replication can only work under these circumstances: ... IIS and its FTP services are running on both ends of the synch ...
    (microsoft.public.access.replication)
  • Synch errors in 2008
    ... We just moved our SQL 2005 database to 2008 and setup merge replication. ... Management Studio on the laptops (some with XP Pro and some with Vista ... If we synch as an administrator then all ...
    (microsoft.public.sqlserver.replication)
  • RE: TSI Sync problem (Path not found)
    ... synch being closed. ... remote database without closing the DB on the local machine first. ...
    (microsoft.public.access.replication)
  • Re: Synchronization Module
    ... The master copy is on my local server. ... So there is no backend at this point because I haven't been able ... to your question is that it is a database that has the data tables ... If you continue to synch your unsplit ...
    (microsoft.public.access.queries)