Replication Design Question/Inquiry

[adb]

I came up with a replication configuration that is basically the result
of
all the restrictions of replication as well as the restrictions of
allowable software on work PC's and I was curious if anyone would point
out
any flaws or possible problems with the design.

I'm limited to Access 2000 professional edition (Thus I cannot use
indirect
sync or internet sync) and cannot install anythird party controls (i.e.
the
TSI Synchronizer). All wizards are unavailable (as in anything under
the
Tools~Replication menu). So I'm left with Direct Sync.

I have two different networks in which my design master is installed.
I
have a setup program on each network that creates the design master,
and
makes a replica of the design master. I have the db split into front
and
back ends. So on each network there is the design master, a replica,
and a
front end mdb. In order to sync the two, I output the data from the
first
design master (DM1) into an excel sheet and that file is emailed to the
second network. A person there will then click a button that uses vba
to
import the data from the excel sheet into their replica (R2) into
temporary
tables. VBA then goes through and compares the new data in the
temporary
tables to the data in the actual tables of R2 and updates any fields in
the
actual tables only if the data is different. Any new records in the
excel
sheet that aren't found in the actual tables are automatically added
directly to the actual table. Then the design master 2 (DM2) does an
import/export sync with R2, and I have a manual reconcilation function
that
compares the two records when there is a conflict, letting the user
choose
which record to keep or keeping parts of both. After all
reconciliations
are made DM2 performs another direct sync to R2, but this time only an
export so that R2 gets all the new data from DM2 that occurred after
the
first sync (the manual reconcilations).

The process is then reversed with DM2 exporting to excel, emailing to
DM1,
importing to R1, syncing export/import DM1 with R1, reconciling,
syncing
export DM1 with R1.

The main issues I can see are that it is possible if one database1
imports
excel sheets twice in a row, without the database2 importing a one from
database1, then data can be overwritten because data didn't change in
the
design master, but it did in the replica, so there is no conflict and
the
new data automatically overwrites the design master data. However,
because
of all the limitations I have this is something I can deal with because
I
backup all the rows that get updated before they are updated. Thus I
can
manually go back and fix something if needed.

Just curious what people thought about this design, and if there are
some
glaring bad design moves. So any input anyone has, I'm more than
welcome
to hear it and would appreciate it.

Also, I set the replica retention period to I think either 1000 or 2000
days. The db will only be in use until no more than 2008. Any negative
things about using a high retention time?

Thanks,
Aaron

[David W. Fenton]

"adb" <ad*****@yahoo.com> wrote in
news:11**********************@l41g2000cwc.googlegr oups.com:

I came up with a replication configuration that is basically the
result of
all the restrictions of replication as well as the restrictions of
allowable software on work PC's and I was curious if anyone would
point out
any flaws or possible problems with the design.

I'm limited to Access 2000 professional edition (Thus I cannot use
indirect
sync or internet sync) and cannot install anythird party controls
(i.e. the
TSI Synchronizer). All wizards are unavailable (as in anything
under the
Tools~Replication menu). So I'm left with Direct Sync.

I have two different networks in which my design master is
installed. I
have a setup program on each network that creates the design
master, and
makes a replica of the design master. I have the db split into
front and
back ends. So on each network there is the design master, a
replica, and a
front end mdb. . . .
I don't know what you *meant* to describe, but you can't have more
than one design master in a single replica set, so it really isn't
possible that you have a design master on each network segment.
. . . In order to sync the two, I output the data from
the first
design master (DM1) into an excel sheet and that file is emailed
to the second network. A person there will then click a button
that uses vba to
import the data from the excel sheet into their replica (R2) into
temporary
tables. VBA then goes through and compares the new data in the
temporary
tables to the data in the actual tables of R2 and updates any
fields in the
actual tables only if the data is different. Any new records in
the excel
sheet that aren't found in the actual tables are automatically
added directly to the actual table. Then the design master 2
(DM2) does an import/export sync with R2, and I have a manual
reconcilation function that
compares the two records when there is a conflict, letting the
user choose
which record to keep or keeping parts of both. After all
reconciliations
are made DM2 performs another direct sync to R2, but this time
only an export so that R2 gets all the new data from DM2 that
occurred after the
first sync (the manual reconcilations).
In other words, you're not using replication at all for your
synchronization, and your design masters aren't members of the same
replica set.
The process is then reversed with DM2 exporting to excel, emailing
to DM1,
importing to R1, syncing export/import DM1 with R1, reconciling,
syncing
export DM1 with R1.

The main issues I can see are that it is possible if one database1
imports
excel sheets twice in a row, without the database2 importing a one
from database1, then data can be overwritten because data didn't
change in the
design master, but it did in the replica, so there is no conflict
and the
new data automatically overwrites the design master data.
However, because
of all the limitations I have this is something I can deal with
because I
backup all the rows that get updated before they are updated. Thus
I can
manually go back and fix something if needed.
Manually recreating the functionality of Jet replication is
impossible.

You need to talk to your management and get them to recognize that
you are spending hours and $$$$$ to incompletely implement something
that could be easily implemented with Replication Manager and the
TSI Synchronizer if they only bought you the Developers Edition and
allowed you to install 3rd-party controls.

Your management is being penny-wise and pound-foolish.
Just curious what people thought about this design, and if there
are some
glaring bad design moves. So any input anyone has, I'm more than
welcome
to hear it and would appreciate it.
I think it's a valiant effort, but you're bound for disaster.

And all of that because of STUPID MANAGEMENT who can't tell the
difference between valid reasons to purchase the Developers Edition
(you need only one copy -- for *you*) and to allow installation of
3rd-party controls.
Also, I set the replica retention period to I think either 1000 or
2000 days. The db will only be in use until no more than 2008.
Any negative things about using a high retention time?

You're only using replication in a remarkably trivial fashion, and
it's probably safe.

You really need to talk to whoever set the restrictions on your
environment and explain to them the problems they've made for you.

What you've described is very Dilbert-esque in terms of management
-- collossally stupid policies that prevent people from actually
doing their jobs efficiently and reliably.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[adb]

Thanks for the response.
Right, the design masters aren't part of the same replica set. They are
each in their own set. I'm just using them in a way as a way to make
use of the synchronization ability between the design master and the
replica. I have already implemented the system and it works as far as
I can tell, but I know with replication there are lots of hidden things
that can come up with over time.
Unfortunately it's not my management. They'd be more than willing to
spring for some tools and developer edition. The problem is it's for a
government agency with huge restrictions on their network, which none
of us have any control over. My design is a result of make due with
what you have.