DIY Replication/Synchronization

[Guest]

We have a small number of people who would like to use a Timesheet
application out of the office. I'd rather not climb the Replication learning
curve, but wanted to get opinions from experts here about the strategy I'm
considering.

The main data input form and its subform are each based on a single table.
I plan to put a renamed subset of these two tables in a separate back-end
file, stored on the user's hard drive, and linked into the front-end.

When the user loads the database, the form will be loaded based on the dummy
tables. On exit, a connection to the server will be checked--if it exists,
update queries will be run to transfer the records to the genuine tables, so
that management has the most up-to-date data available. If not, it will
simply exit.

It seems that it should work to me, but I wonder if I'm missing something.
Does anyone have an opinion?

 

[Guest]

Provided the updates being undertaken remotely are either (a) mutually
exclusive and consequently would not give rise to any conflicts, or (b) you
are happy to allow the latest update to take precedence if the same rows are
being updated by more than one remote user, then there is little point in
using replication.

I have done this successfully myself, and from the sounds of it, in a rather
more complex scenario than you are contemplating. The update process was
straightforward enough nevertheless, and it was tedious rather than difficult
to write all the 'append' and 'update' queries involved. Once the donkey
work of developing the routines was done, however, it worked fine in practice.

In my case the updates were triggered manually by the user rather than
automatically when connecting to the network, but that's not a material
difference.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for your input, Ken. As usual, on target and clear.

The beauty of this situation is that the "genuine" target is itself based on
dummy tables that hold the current timesheet for each user. To simplify data
entry, the detail table has one row per project and non-normalized fields
Day1, Day2, etc. Users only have access to their own "Timesheet" record, so
no potential write conflicts exist. When the period is complete, the user
"posts" his two weeks of data to normalized tables and the period is reset.

I could actually skip the intermediate updates, since the period in process
is in limbo and not used for billing, but occasionally management likes to
see where people are within the current period.

But, Ken..."Donkey work"? I always thought of it as "God is in the
details."

Thanks again.
Sprinks

 

[David W. Fenton]

Provided the updates being undertaken remotely are either (a)
mutually exclusive and consequently would not give rise to any
conflicts, or (b) you are happy to allow the latest update to take
precedence if the same rows are being updated by more than one
remote user, then there is little point in using replication.

Those are exactly the conditions in which using replication is
itself really straightforward, as you don't have to worry about
conflicts (i.e., losing data).

The main issue is how the users would synch. If they are going out
of the office, entering their timesheets, and returning to the
office where they are then connected to the LAN, replicatoin is a
piece of cake -- you can use direct replication, which requires very
little code to work from your front end.

If they need to update from out in the field, that's a different
matter entirely.

But given the kind of updates that were described by the original
poster, I'm pretty sure it's a LAN connection back in the office
that would be used to apply the updates, and in that case, I simply
can't recommend going through the muck of writing your own synch
code when replication will simply do it for you with only a couple
of lines of code.

 

[aaron.kempf]

if you're too ****ing stupid to 'learn replication' then hire someone
that will.

MDB is for ****ing babies; for starters-- you do not NEED to learn 2
different dialects of the SQL language.

MDB is _WORTHLESS_

lose the training wheels; and go and take a class on SQL Server.

'oh is it too hard for you'

ROFL, some dude in India would be glad to work TWICE as hard for HALF
the money.

****ing pussy

-Aaron

 

[Guest]

David,

Thanks for your input. My issue is that I don't know what I don't know. I
feel that in order to apply Replication in even this easy scenario, I need to
understand how it works fully, which will take time. Moreover, since only
two tables are involved (themselves dummy tables), the required queries are
simple--empty and copy.

So in this case, I see the labor investment as about equal. If I
anticipated needing to provide Replication in the future, I'd probably bite
the bullet now, but I don't, therefore, custom code that I understand and can
support makes more sense to me.

Thanks again.
Sprinks

 

[David W. Fenton]

My issue is that I don't know what I don't know. I
feel that in order to apply Replication in even this easy
scenario, I need to understand how it works fully, which will take
time. Moreover, since only two tables are involved (themselves
dummy tables), the required queries are simple--empty and copy.

Dummy tables don't work with replication, if you mean by that work
tables with temporary data that's deleted after it's served its
purpose.

 

[Guest]

David,

The sense in which the server-based tables are "dummy" tables (Timesheet and
Timesheet Detail) is that they hold the time spent on various projects only
for the current 2-week period. There is one record per employee. Timesheet
Detail is non-normalized, with fields Day1, Day2, ..., Day14. It is designed
this way to simplify the data input--the user enters the project just once
and cursors to the column for the day. At the end of the period, the user
presses a "Post" button, which sends the data to normalized tables & resets
the timesheet to the next period. So, there always exists one record per
employee.

The hard disk-based tables are provided in lieu of implementing
Replication--at resync time, the server-based tables will be updated with the
data in the hard-disk.

Sprinks

 

[David W. Fenton]

The sense in which the server-based tables are "dummy" tables
(Timesheet and Timesheet Detail) is that they hold the time spent
on various projects only for the current 2-week period. There is
one record per employee. Timesheet Detail is non-normalized, with
fields Day1, Day2, ..., Day14. It is designed this way to
simplify the data input--the user enters the project just once and
cursors to the column for the day. At the end of the period, the
user presses a "Post" button, which sends the data to normalized
tables & resets the timesheet to the next period. So, there
always exists one record per employee.

Then those tables would not be replicated, right?

 

[Guest]

No, they will not be replicated. I'm using this strategy in lieu of
replication.

 

[David W. Fenton]

I'm using this strategy in lieu of
replication.

And I'm telling you you're making much more work for yourself than
you need to.

 

[punjab_tom]

learn how to do web development babe

or if you really need disconnected then put MSDE on the laptops and
sync into SQL Server

MDB is for babies

-Tom

 

[Guest]

David,

I'm all for saving effort. As a professional in another field supplying
data services to our small office part-time, and having code built in my
library that does nearly every piece required, I'm judging that the effort to
learn about replication to the point at which I'd feel comfortable deploying
it to be greater than writing the code myself.

But maybe I'm wrong. You mentioned I'd only need a few lines of code to use
replication in this scenario. If you can be more specific about what would
be required, I'd gladly rethink my strategy.

Sprinks

 

[aaron.kempf]

yeah; let's all reinvent the wheel

(SQL SERVER HAS A RELIABLE REPLICATION TOOLSET, MDB IS FOR BABIES)

 

[David W. Fenton]

I'm all for saving effort. As a professional in another field
supplying data services to our small office part-time, and having
code built in my library that does nearly every piece required,
I'm judging that the effort to learn about replication to the
point at which I'd feel comfortable deploying it to be greater
than writing the code myself.

But maybe I'm wrong. You mentioned I'd only need a few lines of
code to use replication in this scenario. If you can be more
specific about what would be required, I'd gladly rethink my
strategy.

Check out the Google Groups archive for
microsoft.public.access.replication. The code for implementing
simple direct replication has been posted there a gazillion times.

 

[aaron.kempf]

you don't need CODE for replication

you need SQL Server