Synchronising DB's: Best approach?

[vavroom]

Hello,

I am at the design stage of designing an application. There is an MS
DB in place currently, but it's old, and certainly not optimised, and
as we're expanding to other sites as well, I was asked to create
something solid.

The data in a couple tables needs to be synchronised between sites.
Currently, only two sites are using the DB, but up to 15 locations will
be joining.

I've looked at replication, which seems like a good solution, but with
many pitfalls. One of the problems is that there will NOT be a way to
connect over a LAN (the PTB in their "infinite wisdom" have decreed
that for confidentiality issues, they weren't going to grant anyone
access to the server where the "main" db resides...).

So, can replication by email be done? Seems like it would be a bad
idea, fraught with risks of corruption?

Currently, they export data to CSV, then run an append query from the
CSV. This is kinda ok for someone who has done it, but it's cumbersome
(may be done as often as daily!). I'd like to automate the process and
make it easier, so it's foolproof (well, I know, the moment I do
something foolproof, they'll come up with a better fool, but...)

Any ideas would be appreciated.

 

[vavroom]

Well, trying to understand the requirements and specs better, i was
provided with more information that gives me the direction I'll want to
take.

Turns out that only one location will have data from all sites.
individual sites want to see only the data relevant to them. Which
means some fancy query work. I have an idea of how I'll do this,
wel'll see.

Cheers

Nic

 

[Guest]

hi nic,

i saw you r post and i have the same issue. i wondered if you had found a
solution. your help would be greatly appreciated. thanks richard

 

[vavroom]

i saw you r post and i have the same issue. i wondered if you had found a
solution. your help would be greatly appreciated.

Hello Richard,

In discussing the issues at play with the people who will be using the
database, it became clear that full replication was not only
unecessary, but undesirable.

Each of the locations want to see only the records that are specific to
their own location, and do not want the other locations to see "their"
records (confidentiality issues).

So, one location is the "warehouse", keeps all the data in, and will
be sending new record/receive updated records from each location.

I'm doing a variation on the theme of exporting a CSV by email, with a
lot of under the hood coding. The data I have lends itself well to
this.

The users only have to open a form, put in a date or date range for the
records they need to send back to the central location. They click a
command button, which runs an export of the data into a csv, attaches
it automatically to an email, and sends it "home".

Upon receipt of the email, we save the file wherever we want it. Open
a form, browse for the file in question, click a command button, and
the data gets inserted/updated into the appropriate tables.

My coding is probably not all that elegant, and relied on a LOT of good
advice from folks around here (thanks guys and gals), but it works

If this sounds like something you might want to do, let me know, I'll
gladly share with you.

 

[Guest]

hi nic,

that sound great, i would like to have a look at it, maybe i can share some
other functions i have built that would enhance your system.

i did come accross a really cool bit of software which will automatically
synchronise two databases. it is a very simple peice of kit and can set up a
profile in 5 steps of a wizard. the cost is about $99. i tested it and it
works great. if your interested let me know and i will sedn you a url link
to the site. my e-mail is (e-mail address removed). thanks
richard

 

[Tony Toews]

that sound great, i would like to have a look at it, maybe i can share some
other functions i have built that would enhance your system.

i did come accross a really cool bit of software which will automatically
synchronise two databases. it is a very simple peice of kit and can set up a
profile in 5 steps of a wizard. the cost is about $99. i tested it and it
works great. if your interested let me know and i will sedn you a url link
to the site. my e-mail is (e-mail address removed). thanks
richard

Please post the URL to the newsgroup. I'm sure others would be
interested.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pieter Wijnen]

and you're asking for spam when you post your e-mail in plain text

Pieter

 

[Pieter Wijnen]

and you're asking for spam when you post your e-mail in plain text

Pieter

Please post the URL to the newsgroup. I'm sure others would be
interested.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

--

 

[Tony Toews]

and you're asking for spam when you post your e-mail in plain text

Oddly enough my ISP does a pretty good job of filtering that email
account. Last time I checked when the spam filtering was turned off
I was getting 500 to 600 spams a day. With the spam filtering turned
on I'm only getting 10 or 15 per day.

Besides I've been using the same email account now for about 26,000
postings for about ten years now and I have no interesting in changing
it.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[aaron.kempf]

if you used Access Data Projects and not obsolete MDB; then you
woudln't spend all your time syncing data

-Aaron

 

[Pieter Wijnen]

same for me <g>
and yes, I've seen you around as long as I've been in the group - on and of
depending on what I've been doing, I admit.

Pieter

 

[Pieter Wijnen]

same for me <g>
and yes, I've seen you around as long as I've been in the group - on and of
depending on what I've been doing, I admit.

Pieter

Oddly enough my ISP does a pretty good job of filtering that email
account. Last time I checked when the spam filtering was turned off
I was getting 500 to 600 spams a day. With the spam filtering turned
on I'm only getting 10 or 15 per day.

Besides I've been using the same email account now for about 26,000
postings for about ten years now and I have no interesting in changing
it.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

--

 

[vavroom]

if you used Access Data Projects and not obsolete MDB; then you
woudln't spend all your time syncing data

You assume that it is possible to do so, when I explained in my
original post that one of the problems was there is no common server
available between locations.

 

[aaron.kempf]

no common server?

MSDE is FREEWARE. Use a desktop with a lot of ram.