Re: Homegrown synchronization

Well, I've been playing around with the SQL for synchronizing EXISTING
records in two tables. Eventually, I'm sure I can get it to work in a
TableDef loop, but I can see now that this is going to be really,
really slow (looping through over 100 tables). And I'm just talking
about writing the updated and deleted records between my app and a temp
updateDB on the SAME workstation (no network connection). The largest
table (130,000 rows) takes 40 seconds alone. Add in 99 more tables
(okay they're smaller), but this could probably take at least 10
minutes. And it doesn't really matter whether there have been any
changes to the tables or not. OTOH, Jet direct synchronization takes
only a few seconds when both databases are on the same machine.

A speedier although time-consuming-to-implement method is: when my code
makes an add/edit/delete to a table, at that point write the record to
the temp updateDB database. There would essentially be no effect on
speed in this case. The hard part would be modifying all of the forms
where updates occur to also include writing to an external database.

I'm coming to the conclusion that there just aren't any good
alternatives given the existing IT environment that I have to work in.

Maybe what I should do is stick with replication and direct
synchronization and implement as many processes as possible to protect
the backend replica on the server (the main backend). Alert the user
when a database has lost it's replication (so they can create another
replica for local use). Creating daily backups of the server replica
will probably be required. If the server replica has a problem, then
it can be deleted, removed from the replica set and replaced by a
replica from the most recent "backup" replica (note: MakeReplica will
be used at all times, no file copying).

Hypothetical: Hub backend replica in a star configuration is on the
server at SiteA (server replica). User1 is at SiteB connected to SiteA
by a WAN. User1 tries to direct synchronize his remote replica with
the server replica but an error message comes back saying that
synchronization cannot be accomplished because the server replica is
corrupted or no longer replicated (I hope there is such a message
available).

Can User1 at SiteB safely initiate the replication of a new server
replica at SiteA from an existing "backup" replica also located at
SiteA. Or is this dangerous because of the WAN connection?

rdemyan@xxxxxxxxxxx wrote:
2. use DAO to walk the recordset fields to find updated records.
This is a matter of writing on-the-fly SQL that is something like
this:

UPDATE table1
INNER JOIN table1 ON table1.PK = table2.PK
SET table1.field = table2.field2
WHERE table1.field <> table2.field2


For importing from the temp updateDB, I know which databases to use to
get table1 (currently connected backend file) and table2 (temp
updateDB).

But for exporting, this is not clear. The code will have created the
temp updateDB but it will be empty. I think I'm going to need to make
a copy of the backend file when the app opens. It will essentially be
a snapshot of the backend prior to any updates/edits/deletes.
Copying files on the local PC can be fast (75 MB on my machine takes
about 10 seconds). Then I need to do the join and update the temp
updateDB. I've never written code like that before but I guess if I
just change the Update statement above to:

UPDATE table3.....

table1 = local backend file table
table2 = copy of local backend file table created when app was first
opened during the session
table2 = temp updateDB table

There's probably more clever ways to do this, but this should work,
although speed is important.

.

Relevant Pages

  • Re: Homegrown synchronization
    ... the local backend file that the user is connected to. ... table in the temp updateDB. ... the synchronizer on the server ... protect the backend replica on the server. ...
    (microsoft.public.access.replication)
  • Re: Homegrown synchronization
    ... Your suggestion about applying the filter put the spunk back in me:) ... temp updateDB and it seems to be working well. ... Did you say earlier why Windows Terminal Server was out of the ... protect the backend replica on the server. ...
    (microsoft.public.access.replication)
  • Re: Test to see if another remote is syncing with the hub replica
    ... direct synchronization at this location. ... But don't you still need to install the Jet Synchronizer on the server. ... making replica backups, restoring replica backups (in case the server ...
    (microsoft.public.access.replication)
  • Re: Test to see if another remote is syncing with the hub replica
    ... direct synchronization at this location. ... But don't you still need to install the Jet Synchronizer on the server. ... making replica backups, restoring replica backups (in case the server ...
    (microsoft.public.access.replication)
  • Re: Two or more users attempting to sync with the hub at the same time
    ... I would *never* implement simultaneous synchronization with DIRECT ... my laptop. ... Then I created a replica in another location on the ... I have a small test app that I coded to create replicas and ...
    (microsoft.public.access.replication)